2024W AML 2103 2 Visualization for AI and ML

Assignment 1

Jayachandhran Saravanan (C0910392)

Inferernce and Results:¶

Q1:¶
  • In this task, the function to create a quarterly summary for the given feature variable is created
  • Extensive details on the transaction count, average, mean, and median values are represented
  • Each service area performance is observed and the highest value of sum is "Children's Family Services 2016.0 Q4"
  • most of the plots were unstable
Q2:¶
  • the year 2016 saw the highest spike ever
  • Regional Enterprise (service area has more than 5 spikes in the given 4 years)
  • 12 out of 25 service areas have permanent (non-spike behaviour)
  • percentage difference and z score on consecutive transactions are calculated
Q3:¶
  • "AMAZON UK MARKETPLACE" creditor has the highest level of repeatation in account over 25 instances
  • there are 700+ creditors mapped bettern 2-3 accounts
Q4:¶
  • Using the spending behavior (transaction sum, average and count) the service area (25 in numbers) are clustered
  • K means gave 3 clusters with unstable distribution with cluster1-3 cluster2-18 cluster3-2
  • Using HAC, the data points are branched using the mixed linkage method, the clusters are displayed below
Q5:¶
  • using simple standard deviation, the data points against time(week | consecutive) are checked for any abnormal behavior or sudden difference in patterns
  • those are potential for observation to check for anomaly transaction
  • Education service has more number of anomalies present
  • The highest of 800% surge is given in an account --> which has a mean of 400(CAD/USD)
  • various experimentations on the fraud/anomaly is carried out and attached in appendix

12 reproducible functions are created for easy customization and automation¶

Data set 3 is experimented but the analysis is not adding proper value so the null and non-useful values are removed¶

below the script and outputs are attached¶

Importing required packages¶

In [1]:
import warnings
warnings.filterwarnings("ignore")
import pandas as pd
import numpy as np
import re
import matplotlib.pyplot as plt
import seaborn as sns
from IPython.display import display, HTML
from matplotlib.ticker import FuncFormatter
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
from scipy.cluster.hierarchy import dendrogram, linkage

loading the data | Pandas¶

In [2]:
#loading the dataset given
df1=pd.read_csv('data_1.csv')
df3=pd.read_csv('data_3.csv')
df4=pd.read_csv('data_4.csv')
df2 = pd.read_csv("data_2.txt")
# df3=pd.read_('data_3.txt')
In [3]:
#checking the given data

data=['df1','df2','df3','df4']
colm={}

for i,col in enumerate([df1,df2,df3,df4]):
    colm[data[i]]=set(col.columns)

Checking column names and data preprocessing experiments¶

In [4]:
colm
Out[4]:
{'df1': {'Account Description',
  'Creditor',
  'JV Date',
  'JV Reference',
  'JV Value',
  'Service Area',
  'Transaction Date'},
 'df2': {'Account Description',
  'Creditor',
  'Journal Date',
  'Journal Reference',
  'Service Area',
  'Total'},
 'df3': {'Account Description',
  'Creditor',
  'Journal Date',
  'Journal Reference',
  'Service Area',
  'Total'},
 'df4': {'FIN.INET CONVERSION',
  'FIN.ORIGINAL CURRENCY AMOUNT',
  'FIN.ORIGINAL ISO CURRENCY CODE SYMBOL',
  'FIN.POSTING DATE',
  'FIN.TRANSACTION AMOUNT',
  'FIN.TRANSACTION DATE',
  'MCH.CITY NAME',
  'MCH.MERCHANT NAME'}}
In [5]:
#since we can remove the JV Date from the first data
df1.drop(columns=['JV Date'],inplace=True)
In [6]:
df1.head()
Out[6]:
Service Area Account Description Creditor Transaction Date JV Reference JV Value
0 Childrens Services IT Services 123-REG.CO.UK 23/04/2014 93 143.81
1 Childrens Services Other Services ACCESS EXPEDITIONS 03/04/2014 111 6,000.00
2 Childrens Services Equipment and Materials Repair AFE SERVICELINE 02/04/2014 6 309.38
3 Childrens Services Equipment and Materials Repair AFE SERVICELINE 02/04/2014 7 218.76
4 Childrens Services Building Repairs & Maintenance ALLSOP & FRANCIS 15/04/2014 381 306
In [7]:
df2.head()
Out[7]:
Service Area Account Description Creditor Journal Date Journal Reference Total
0 Assurance Miscellaneous Expenses 43033820 COSTA COFFEE 18/08/2015 5043.0 2
1 Children's Family Services Miscellaneous Expenses 99 PLUS DISCOUNT MART 08/06/2015 4184.0 29.97
2 Children's Family Services E19 - Learning Resources 99P STORES LTD 07/12/2015 6278.0 34.65
3 Children's Family Services Equipment and Materials Purcha 99P STORES LTD 18/08/2015 5041.0 10.72
4 Children's Family Services Subsistence CHOPSTIX00000000000 21/05/2015 5750.0 33.7
In [8]:
column_names=['Service Area','Account Description',
  'Creditor',
  'Transaction Date',
  'JV Reference',
  'JV Value' ]
In [9]:
for i,col in enumerate([df1,df2,df3]):
    col.set_axis(column_names,axis=1,inplace=True)
In [10]:
data=['df1','df2','df3']
colm={}

for i,col in enumerate([df1,df2,df3]):
    colm[data[i]]=set(col.columns)
In [11]:
set.intersection(*colm.values())#all the columns are common here
Out[11]:
{'Account Description',
 'Creditor',
 'JV Reference',
 'JV Value',
 'Service Area',
 'Transaction Date'}
In [12]:
df1.head()
Out[12]:
Service Area Account Description Creditor Transaction Date JV Reference JV Value
0 Childrens Services IT Services 123-REG.CO.UK 23/04/2014 93 143.81
1 Childrens Services Other Services ACCESS EXPEDITIONS 03/04/2014 111 6,000.00
2 Childrens Services Equipment and Materials Repair AFE SERVICELINE 02/04/2014 6 309.38
3 Childrens Services Equipment and Materials Repair AFE SERVICELINE 02/04/2014 7 218.76
4 Childrens Services Building Repairs & Maintenance ALLSOP & FRANCIS 15/04/2014 381 306
In [13]:
df2.head()
Out[13]:
Service Area Account Description Creditor Transaction Date JV Reference JV Value
0 Assurance Miscellaneous Expenses 43033820 COSTA COFFEE 18/08/2015 5043.0 2
1 Children's Family Services Miscellaneous Expenses 99 PLUS DISCOUNT MART 08/06/2015 4184.0 29.97
2 Children's Family Services E19 - Learning Resources 99P STORES LTD 07/12/2015 6278.0 34.65
3 Children's Family Services Equipment and Materials Purcha 99P STORES LTD 18/08/2015 5041.0 10.72
4 Children's Family Services Subsistence CHOPSTIX00000000000 21/05/2015 5750.0 33.7
In [14]:
df=pd.concat([df1,df2,df3],ignore_index=True)
In [15]:
df.head()
Out[15]:
Service Area Account Description Creditor Transaction Date JV Reference JV Value
0 Childrens Services IT Services 123-REG.CO.UK 23/04/2014 93.0 143.81
1 Childrens Services Other Services ACCESS EXPEDITIONS 03/04/2014 111.0 6,000.00
2 Childrens Services Equipment and Materials Repair AFE SERVICELINE 02/04/2014 6.0 309.38
3 Childrens Services Equipment and Materials Repair AFE SERVICELINE 02/04/2014 7.0 218.76
4 Childrens Services Building Repairs & Maintenance ALLSOP & FRANCIS 15/04/2014 381.0 306
In [16]:
df.shape[0]==sum([df1.shape[0],df2.shape[0],df3.shape[0]])
Out[16]:
True

EDA¶

In [17]:
#getting the details of the combined data set

def get_data_info(data):
    print("shape of the dataframe: ",data.shape,"\n","="*50)
    print("size of the data: ",data.size,"\n","="*50)
    print("duplicate values present: ",data.duplicated().sum(),"\n","="*50)
    print("missing values %: \n",data.isnull().mean()*100,"\n","="*50)
    print("unique values present in the data: \n",data.nunique(),"\n","="*50)
In [18]:
get_data_info(df)

shape of the dataframe:  (12589, 6) 
 ==================================================
size of the data:  75534 
 ==================================================
duplicate values present:  0 
 ==================================================
missing values %: 
 Service Area           0.007943
Account Description    0.015887
Creditor               0.015887
Transaction Date       0.015887
JV Reference           0.015887
JV Value               0.000000
dtype: float64 
 ==================================================
unique values present in the data: 
 Service Area              25
Account Description       67
Creditor                1936
Transaction Date         739
JV Reference           10742
JV Value                6292
dtype: int64 
 ==================================================
In [19]:
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 12589 entries, 0 to 12588
Data columns (total 6 columns):
 #   Column               Non-Null Count  Dtype  
---  ------               --------------  -----  
 0   Service Area         12588 non-null  object 
 1   Account Description  12587 non-null  object 
 2   Creditor             12587 non-null  object 
 3   Transaction Date     12587 non-null  object 
 4   JV Reference         12587 non-null  float64
 5   JV Value             12589 non-null  object 
dtypes: float64(1), object(5)
memory usage: 590.2+ KB
In [20]:
#the values of jv value is in object/string type

df['JV Value']=df['JV Value'].apply(lambda x:(''.join(re.findall(r"\d+",x)) ))
df['JV Value']=df['JV Value'].astype(float)
In [21]:
df['JV Value'].dtype #mission success
Out[21]:
dtype('float64')
In [22]:
df['Transaction Date']=pd.to_datetime(df['Transaction Date'])
df['date']=df['Transaction Date'].dt.day
In [23]:
df['month']=df['Transaction Date'].dt.month
In [24]:
df['year']=df['Transaction Date'].dt.year
df['QT']=df['Transaction Date'].dt.quarter
df['QT_validation']=df['Transaction Date'].dt.to_period('Q')

df['final_qr'] = pd.PeriodIndex(df['Transaction Date'], freq='Q')
In [25]:
df['Quarter'] = df['QT'].map(lambda x: "Q{}".format(x)).str[:-2]
In [26]:
df.head()
Out[26]:
Service Area Account Description Creditor Transaction Date JV Reference JV Value date month year QT QT_validation final_qr Quarter
0 Childrens Services IT Services 123-REG.CO.UK 2014-04-23 93.0 14381.0 23.0 4.0 2014.0 2.0 2014Q2 2014Q2 Q2
1 Childrens Services Other Services ACCESS EXPEDITIONS 2014-03-04 111.0 600000.0 4.0 3.0 2014.0 1.0 2014Q1 2014Q1 Q1
2 Childrens Services Equipment and Materials Repair AFE SERVICELINE 2014-02-04 6.0 30938.0 4.0 2.0 2014.0 1.0 2014Q1 2014Q1 Q1
3 Childrens Services Equipment and Materials Repair AFE SERVICELINE 2014-02-04 7.0 21876.0 4.0 2.0 2014.0 1.0 2014Q1 2014Q1 Q1
4 Childrens Services Building Repairs & Maintenance ALLSOP & FRANCIS 2014-04-15 381.0 306.0 15.0 4.0 2014.0 2.0 2014Q2 2014Q2 Q2
In [27]:
df.Quarter.value_counts()
Out[27]:
Q1    3272
Q3    3216
Q4    3168
Q2    2931
Qn       2
Name: Quarter, dtype: int64
In [28]:
get_data_info(df)

shape of the dataframe:  (12589, 13) 
 ==================================================
size of the data:  163657 
 ==================================================
duplicate values present:  0 
 ==================================================
missing values %: 
 Service Area           0.007943
Account Description    0.015887
Creditor               0.015887
Transaction Date       0.015887
JV Reference           0.015887
JV Value               0.000000
date                   0.015887
month                  0.015887
year                   0.015887
QT                     0.015887
QT_validation          0.015887
final_qr               0.015887
Quarter                0.000000
dtype: float64 
 ==================================================
unique values present in the data: 
 Service Area              25
Account Description       67
Creditor                1936
Transaction Date         739
JV Reference           10742
JV Value                5353
date                      31
month                     12
year                       4
QT                         4
QT_validation             16
final_qr                  16
Quarter                    5
dtype: int64 
 ==================================================
In [29]:
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 12589 entries, 0 to 12588
Data columns (total 13 columns):
 #   Column               Non-Null Count  Dtype         
---  ------               --------------  -----         
 0   Service Area         12588 non-null  object        
 1   Account Description  12587 non-null  object        
 2   Creditor             12587 non-null  object        
 3   Transaction Date     12587 non-null  datetime64[ns]
 4   JV Reference         12587 non-null  float64       
 5   JV Value             12589 non-null  float64       
 6   date                 12587 non-null  float64       
 7   month                12587 non-null  float64       
 8   year                 12587 non-null  float64       
 9   QT                   12587 non-null  float64       
 10  QT_validation        12587 non-null  period[Q-DEC] 
 11  final_qr             12587 non-null  period[Q-DEC] 
 12  Quarter              12589 non-null  object        
dtypes: datetime64[ns](1), float64(6), object(4), period[Q-DEC](2)
memory usage: 1.2+ MB
In [30]:
df.describe().T
Out[30]:
count mean std min 25% 50% 75% max
JV Reference 12587.0 6126.846906 3248.967457 1.0 3943.5 5795.0 8847.5 12136.0
JV Value 12589.0 14626.548495 541065.815394 0.0 298.0 1600.0 5825.0 47104401.0
date 12587.0 15.853976 8.456099 1.0 9.0 16.0 23.0 31.0
month 12587.0 6.467625 3.489941 1.0 3.0 7.0 10.0 12.0
year 12587.0 2015.268610 0.950082 2014.0 2014.0 2015.0 2016.0 2017.0
QT 12587.0 2.498927 1.128441 1.0 1.0 3.0 4.0 4.0
In [31]:
df['QT_validation'].value_counts()
Out[31]:
2016Q3    1098
2016Q1    1091
2015Q3    1087
2016Q4    1072
2014Q4    1057
2016Q2    1006
2015Q4     974
2014Q2     933
2014Q3     923
2017Q1     919
2015Q1     917
2015Q2     898
2014Q1     345
2017Q3     108
2017Q2      94
2017Q4      65
Freq: Q-DEC, Name: QT_validation, dtype: int64
In [32]:
df['QT_validation'].unique()
Out[32]:
<PeriodArray>
['2014Q2', '2014Q1', '2014Q4', '2014Q3', '2015Q1', '2015Q3', '2015Q2',
 '2015Q4', '2016Q1', '2016Q3', '2016Q4', '2016Q2',    'NaT', '2017Q1',
 '2017Q4', '2017Q2', '2017Q3']
Length: 17, dtype: period[Q-DEC]

understanding the data distribution¶

In [ ]:
#seperate the plot granular by passing the values clearly 
sns.pairplot(df,hue='Service Area')
plt.show()

TASK 1¶

In [ ]:
#understanding the service area presentin the data

display(df['Service Area'].unique())
print("total_unique service area",df['Service Area'].nunique())
display(HTML("<hr>"))
display(df['Service Area'].value_counts())
In [36]:
temp = df.groupby('Service Area')
for i in temp:
    display(i)

('Adults and Communities',
                 Service Area  Account Description             Creditor  \
 170   Adults and Communities          Subsistence   KING SOLOMON HOTEL   
 204   Adults and Communities  Travelling Expenses   OYSTER BUS AUTOTOP   
 214   Adults and Communities          Subsistence          PREMIER INN   
 215   Adults and Communities          Subsistence          PREMIER INN   
 307   Adults and Communities       Other Services  VOUCHER EXPRESS B2B   
 ...                      ...                  ...                  ...   
 8172  Adults and Communities  Travelling Expenses   OYSTER BUS AUTOTOP   
 8173  Adults and Communities  Travelling Expenses   OYSTER BUS AUTOTOP   
 8174  Adults and Communities  Travelling Expenses   OYSTER BUS AUTOTOP   
 8175  Adults and Communities  Travelling Expenses     OYSTER ISSUE TEL   
 8176  Adults and Communities           Venue Hire         SARACENS LTD   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 170        2014-11-04         288.0     256.0   4.0   11.0  2014.0  4.0   
 204        2014-04-14         144.0      20.0  14.0    4.0  2014.0  2.0   
 214        2014-04-30         289.0    8155.0  30.0    4.0  2014.0  2.0   
 215        2014-04-30         290.0    8155.0  30.0    4.0  2014.0  2.0   
 307        2014-07-04         190.0     400.0   4.0    7.0  2014.0  3.0   
 ...               ...           ...       ...   ...    ...     ...  ...   
 8172       2017-02-27       11595.0    2000.0  27.0    2.0  2017.0  1.0   
 8173       2017-03-27       12031.0    2000.0  27.0    3.0  2017.0  1.0   
 8174       2016-09-29        9657.0    4000.0  29.0    9.0  2016.0  3.0   
 8175       2016-12-13       10621.0    1000.0  13.0   12.0  2016.0  4.0   
 8176       2016-09-15        9474.0   83333.0  15.0    9.0  2016.0  3.0   
 
      QT_validation final_qr Quarter  
 170         2014Q4   2014Q4      Q4  
 204         2014Q2   2014Q2      Q2  
 214         2014Q2   2014Q2      Q2  
 215         2014Q2   2014Q2      Q2  
 307         2014Q3   2014Q3      Q3  
 ...            ...      ...     ...  
 8172        2017Q1   2017Q1      Q1  
 8173        2017Q1   2017Q1      Q1  
 8174        2016Q3   2016Q3      Q3  
 8175        2016Q4   2016Q4      Q4  
 8176        2016Q3   2016Q3      Q3  
 
 [278 rows x 13 columns])
('Assurance',
      Service Area    Account Description             Creditor  \
 2729    Assurance   Legal and Court Fees  LBB CIVIC CENTRE PA   
 2737    Assurance    Travelling Expenses   LUL TICKET OFFICE.   
 2883    Assurance  Professional Services          WWW.192.COM   
 3337    Assurance  Books-CDs-Audio-Video  Amazon Svcs EuropeS   
 3597    Assurance    Travelling Expenses                 TSGN   
 ...           ...                    ...                  ...   
 8365    Assurance  Vehicle Running Costs      TFL ROAD CHARGE   
 8366    Assurance  Vehicle Running Costs      TFL ROAD CHARGE   
 8367    Assurance  Vehicle Running Costs      TFL ROAD CHARGE   
 8368    Assurance  Vehicle Running Costs      TFL ROAD CHARGE   
 8369    Assurance             Venue Hire     ALEXANDRA PALACE   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 2729       2014-11-13        3928.0       4.0  13.0   11.0  2014.0  4.0   
 2737       2014-11-19        3929.0     114.0  19.0   11.0  2014.0  4.0   
 2883       2014-06-11        3711.0    8994.0  11.0    6.0  2014.0  2.0   
 3337       2015-01-29        4789.0     279.0  29.0    1.0  2015.0  1.0   
 3597       2015-01-26        4788.0      21.0  26.0    1.0  2015.0  1.0   
 ...               ...           ...       ...   ...    ...     ...  ...   
 8365       2017-01-18       10833.0    1150.0  18.0    1.0  2017.0  1.0   
 8366       2017-01-24       10834.0    1150.0  24.0    1.0  2017.0  1.0   
 8367       2017-01-25       10835.0    1150.0  25.0    1.0  2017.0  1.0   
 8368       2017-01-30       10837.0    1150.0  30.0    1.0  2017.0  1.0   
 8369       2016-02-05        7960.0  434220.0   5.0    2.0  2016.0  1.0   
 
      QT_validation final_qr Quarter  
 2729        2014Q4   2014Q4      Q4  
 2737        2014Q4   2014Q4      Q4  
 2883        2014Q2   2014Q2      Q2  
 3337        2015Q1   2015Q1      Q1  
 3597        2015Q1   2015Q1      Q1  
 ...            ...      ...     ...  
 8365        2017Q1   2017Q1      Q1  
 8366        2017Q1   2017Q1      Q1  
 8367        2017Q1   2017Q1      Q1  
 8368        2017Q1   2017Q1      Q1  
 8369        2016Q1   2016Q1      Q1  
 
 [344 rows x 13 columns])
('CSG Managed Budget',
             Service Area             Account Description             Creditor  \
 369   CSG Managed Budget  Private Contractors - Third Pa   Am.UK Xing FOR LAT   
 396   CSG Managed Budget           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 397   CSG Managed Budget           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 398   CSG Managed Budget           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 399   CSG Managed Budget           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 400   CSG Managed Budget           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 401   CSG Managed Budget           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 402   CSG Managed Budget           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 403   CSG Managed Budget           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 491   CSG Managed Budget            Legal and Court Fees  CAPITA BUS SVS-MOTO   
 529   CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 530   CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 531   CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 532   CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 606   CSG Managed Budget                  Other Services           MR BOX LTD   
 769   CSG Managed Budget  Equipment and Materials Purcha  WWW.CLOTHES2ORDER.C   
 843   CSG Managed Budget           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 889   CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 890   CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 891   CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1001  CSG Managed Budget                     IT Services     SURVEYMONKEY.COM   
 1204  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1205  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1209  CSG Managed Budget           Professional Services     IIA - UK IRELAND   
 1542  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1543  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1544  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1856  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1857  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1858  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1859  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 1860  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 2254  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 2255  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 2256  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 2257  CSG Managed Budget            Legal and Court Fees   HMCOURTS-SERVICE.G   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 369        2014-09-05         807.0    2748.0   5.0    9.0  2014.0  3.0   
 396        2014-05-23         814.0     133.0  23.0    5.0  2014.0  2.0   
 397        2014-05-23         815.0    1798.0  23.0    5.0  2014.0  2.0   
 398        2014-12-05         808.0     289.0   5.0   12.0  2014.0  4.0   
 399        2014-12-05         809.0     486.0   5.0   12.0  2014.0  4.0   
 400        2014-12-05         810.0    3095.0   5.0   12.0  2014.0  4.0   
 401        2014-05-14         811.0    1498.0  14.0    5.0  2014.0  2.0   
 402        2014-05-23         812.0    4629.0  23.0    5.0  2014.0  2.0   
 403        2014-05-23         813.0    4499.0  23.0    5.0  2014.0  2.0   
 491        2014-05-30         816.0    4788.0  30.0    5.0  2014.0  2.0   
 529        2014-05-22         697.0  120000.0  22.0    5.0  2014.0  2.0   
 530        2014-05-30         939.0  433800.0  30.0    5.0  2014.0  2.0   
 531        2014-05-22         498.0  780000.0  22.0    5.0  2014.0  2.0   
 532        2014-05-22         497.0  677700.0  22.0    5.0  2014.0  2.0   
 606        2014-05-05         805.0    5988.0   5.0    5.0  2014.0  2.0   
 769        2014-09-05         806.0  123768.0   5.0    9.0  2014.0  3.0   
 843        2014-12-06        1194.0    4499.0   6.0   12.0  2014.0  4.0   
 889        2014-06-17        1245.0  497700.0  17.0    6.0  2014.0  2.0   
 890        2014-06-17        1246.0  402900.0  17.0    6.0  2014.0  2.0   
 891        2014-01-07        1247.0     795.0   7.0    1.0  2014.0  1.0   
 1001       2014-05-06        1193.0     299.0   6.0    5.0  2014.0  2.0   
 1204       2014-07-22        1607.0  245000.0  22.0    7.0  2014.0  3.0   
 1205       2014-07-22        1606.0  228000.0  22.0    7.0  2014.0  3.0   
 1209       2014-07-29        1578.0     173.0  29.0    7.0  2014.0  3.0   
 1542       2014-08-19        1915.0  220500.0  19.0    8.0  2014.0  3.0   
 1543       2014-05-08        1916.0     663.0   8.0    5.0  2014.0  2.0   
 1544       2014-08-19        1914.0  499800.0  19.0    8.0  2014.0  3.0   
 1856       2014-09-23        2235.0    8058.0  23.0    9.0  2014.0  3.0   
 1857       2014-09-23        2236.0     205.0  23.0    9.0  2014.0  3.0   
 1858       2014-02-09        2234.0     477.0   9.0    2.0  2014.0  1.0   
 1859       2014-02-09        2232.0     245.0   9.0    2.0  2014.0  1.0   
 1860       2014-02-09        2233.0    2000.0   9.0    2.0  2014.0  1.0   
 2254       2014-07-10        3495.0     201.0  10.0    7.0  2014.0  3.0   
 2255       2014-10-21        3498.0    4707.0  21.0   10.0  2014.0  4.0   
 2256       2014-10-21        3497.0    4707.0  21.0   10.0  2014.0  4.0   
 2257       2014-10-14        3496.0    4655.0  14.0   10.0  2014.0  4.0   
 
      QT_validation final_qr Quarter  
 369         2014Q3   2014Q3      Q3  
 396         2014Q2   2014Q2      Q2  
 397         2014Q2   2014Q2      Q2  
 398         2014Q4   2014Q4      Q4  
 399         2014Q4   2014Q4      Q4  
 400         2014Q4   2014Q4      Q4  
 401         2014Q2   2014Q2      Q2  
 402         2014Q2   2014Q2      Q2  
 403         2014Q2   2014Q2      Q2  
 491         2014Q2   2014Q2      Q2  
 529         2014Q2   2014Q2      Q2  
 530         2014Q2   2014Q2      Q2  
 531         2014Q2   2014Q2      Q2  
 532         2014Q2   2014Q2      Q2  
 606         2014Q2   2014Q2      Q2  
 769         2014Q3   2014Q3      Q3  
 843         2014Q4   2014Q4      Q4  
 889         2014Q2   2014Q2      Q2  
 890         2014Q2   2014Q2      Q2  
 891         2014Q1   2014Q1      Q1  
 1001        2014Q2   2014Q2      Q2  
 1204        2014Q3   2014Q3      Q3  
 1205        2014Q3   2014Q3      Q3  
 1209        2014Q3   2014Q3      Q3  
 1542        2014Q3   2014Q3      Q3  
 1543        2014Q2   2014Q2      Q2  
 1544        2014Q3   2014Q3      Q3  
 1856        2014Q3   2014Q3      Q3  
 1857        2014Q3   2014Q3      Q3  
 1858        2014Q1   2014Q1      Q1  
 1859        2014Q1   2014Q1      Q1  
 1860        2014Q1   2014Q1      Q1  
 2254        2014Q3   2014Q3      Q3  
 2255        2014Q4   2014Q4      Q4  
 2256        2014Q4   2014Q4      Q4  
 2257        2014Q4   2014Q4      Q4  )
("Children's Education & Skills",
                        Service Area             Account Description  \
 2537  Children's Education & Skills           Books-CDs-Audio-Video   
 2638  Children's Education & Skills                      Food Costs   
 2649  Children's Education & Skills  Cleaning and domestic material   
 2654  Children's Education & Skills  Cleaning and domestic material   
 2724  Children's Education & Skills  Equipment and Materials Purcha   
 ...                             ...                             ...   
 8400  Children's Education & Skills                        Training   
 8401  Children's Education & Skills                        Training   
 8402  Children's Education & Skills                        Training   
 8403  Children's Education & Skills                        Training   
 8404  Children's Education & Skills             Travelling Expenses   
 
                                 Creditor Transaction Date  JV Reference  \
 2537                 Amazon Mktplce EU-U       2014-11-28        3889.0   
 2638                 COMPASS SERVICES UK       2014-11-28        3530.0   
 2649                    ESPRESSO SERVICE       2014-12-11        3546.0   
 2654                 FIRSTCHOICE CATERIN       2014-11-11        3545.0   
 2724                   KAYS DELICATESSEN       2014-11-13        3663.0   
 ...                                  ...              ...           ...   
 8400  FASTER PAYMENT RECEIVED- THANK YOU       2016-04-13        7752.0   
 8401                                 RRC       2016-12-01        6638.0   
 8402                           TRAINLINE       2016-08-11       10164.0   
 8403                   WESTMINSTER FORUM       2016-04-19        7822.0   
 8404                   TRAINLINE.COM LTD       2016-04-10        9705.0   
 
       JV Value  date  month    year   QT QT_validation final_qr Quarter  
 2537    2148.0  28.0   11.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2638     648.0  28.0   11.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2649    7361.0  11.0   12.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2654    3012.0  11.0   11.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2724   15262.0  13.0   11.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 ...        ...   ...    ...     ...  ...           ...      ...     ...  
 8400   13410.0  13.0    4.0  2016.0  2.0        2016Q2   2016Q2      Q2  
 8401      24.0   1.0   12.0  2016.0  4.0        2016Q4   2016Q4      Q4  
 8402    4279.0  11.0    8.0  2016.0  3.0        2016Q3   2016Q3      Q3  
 8403   25200.0  19.0    4.0  2016.0  2.0        2016Q2   2016Q2      Q2  
 8404    7935.0  10.0    4.0  2016.0  2.0        2016Q2   2016Q2      Q2  
 
 [667 rows x 13 columns])
("Children's Family Services",
                      Service Area             Account Description  \
 2468   Children's Family Services  Equipment and Materials Purcha   
 2470   Children's Family Services             Travelling Expenses   
 2471   Children's Family Services  Equipment and Materials Purcha   
 2472   Children's Family Services  Private Contractors - Third Pa   
 2473   Children's Family Services  Private Contractors - Third Pa   
 ...                           ...                             ...   
 12039  Children's Family Services                  Water Services   
 12040  Children's Family Services                  Water Services   
 12041  Children's Family Services                  Water Services   
 12042  Children's Family Services                  Water Services   
 12043  Children's Family Services                  Water Services   
 
                   Creditor Transaction Date  JV Reference  JV Value  date  \
 2468        99P STORES LTD       2014-11-21        3841.0    6692.0  21.0   
 2470   APCOA - NORTHWICK P       2014-11-17        3713.0      61.0  17.0   
 2471    APPLE ONLINE STORE       2014-01-12        3645.0     749.0  12.0   
 2472   ARAGON 356-2248-497       2014-10-11        3978.0      37.0  11.0   
 2473   ARAGON 356-2248-497       2014-10-11        3979.0      27.0  11.0   
 ...                    ...              ...           ...       ...   ...   
 12039         THAMES WATER       2017-01-02       10851.0   32087.0   2.0   
 12040         THAMES WATER       2017-02-02       10853.0   24386.0   2.0   
 12041         THAMES WATER       2016-03-15        7455.0   11699.0  15.0   
 12042     THAMES WATER WEB       2016-05-16        8061.0   18227.0  16.0   
 12043   WWW.AFFINITYFORBUS       2016-12-13       10610.0    3522.0  13.0   
 
        month    year   QT QT_validation final_qr Quarter  
 2468    11.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2470    11.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2471     1.0  2014.0  1.0        2014Q1   2014Q1      Q1  
 2472    10.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2473    10.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 ...      ...     ...  ...           ...      ...     ...  
 12039    1.0  2017.0  1.0        2017Q1   2017Q1      Q1  
 12040    2.0  2017.0  1.0        2017Q1   2017Q1      Q1  
 12041    3.0  2016.0  1.0        2016Q1   2016Q1      Q1  
 12042    5.0  2016.0  2.0        2016Q2   2016Q2      Q2  
 12043   12.0  2016.0  4.0        2016Q4   2016Q4      Q4  
 
 [7672 rows x 13 columns])
("Children's Service DSG",
                  Service Area             Account Description  \
 1430   Children's Service DSG  Equipment and Materials Purcha   
 1440   Children's Service DSG                      Food Costs   
 1473   Children's Service DSG  Equipment and Materials Purcha   
 1474   Children's Service DSG  Equipment and Materials Purcha   
 1498   Children's Service DSG  Equipment and Materials Purcha   
 ...                       ...                             ...   
 12118  Children's Service DSG                        Training   
 12119  Children's Service DSG             Travelling Expenses   
 12120  Children's Service DSG             Travelling Expenses   
 12121  Children's Service DSG             Travelling Expenses   
 12122  Children's Service DSG             Travelling Expenses   
 
                   Creditor Transaction Date  JV Reference  JV Value  date  \
 1430    ARGOS RETAIL GROUP       2014-08-15        1778.0    1294.0  15.0   
 1440       ASDA SUPERSTORE       2014-08-22        1871.0   12318.0  22.0   
 1473   Amazon Mktplce EU-U       2014-08-14        1845.0    1752.0  14.0   
 1474   Amazon Mktplce EU-U       2014-08-14        1846.0   16094.0  14.0   
 1498    CHEAP DISABILITY A       2014-08-13        1844.0    7992.0  13.0   
 ...                    ...              ...           ...       ...   ...   
 12118   WP-THE BRITISH ASS       2016-09-28        9647.0   45000.0  28.0   
 12119     METRO RADIO CARS       2016-11-04        7722.0   36000.0   4.0   
 12120   THE CHILTERN RLYWY       2016-04-21        7846.0    2770.0  21.0   
 12121    TRAINLINE.COM LTD       2016-04-10        9705.0    7936.0  10.0   
 12122   WWW.GL-EDUCATION.C       2016-05-13        8049.0   27452.0  13.0   
 
        month    year   QT QT_validation final_qr Quarter  
 1430     8.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 1440     8.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 1473     8.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 1474     8.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 1498     8.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 ...      ...     ...  ...           ...      ...     ...  
 12118    9.0  2016.0  3.0        2016Q3   2016Q3      Q3  
 12119   11.0  2016.0  4.0        2016Q4   2016Q4      Q4  
 12120    4.0  2016.0  2.0        2016Q2   2016Q2      Q2  
 12121    4.0  2016.0  2.0        2016Q2   2016Q2      Q2  
 12122    5.0  2016.0  2.0        2016Q2   2016Q2      Q2  
 
 [277 rows x 13 columns])
('Childrens Services',
             Service Area             Account Description            Creditor  \
 0     Childrens Services                     IT Services       123-REG.CO.UK   
 1     Childrens Services                  Other Services  ACCESS EXPEDITIONS   
 2     Childrens Services  Equipment and Materials Repair     AFE SERVICELINE   
 3     Childrens Services  Equipment and Materials Repair     AFE SERVICELINE   
 4     Childrens Services  Building Repairs & Maintenance    ALLSOP & FRANCIS   
 ...                  ...                             ...                 ...   
 2268  Childrens Services                      Food Costs   JS ONLINE GROCERY   
 2269  Childrens Services                      Food Costs   JS ONLINE GROCERY   
 2270  Childrens Services                      Food Costs   JS ONLINE GROCERY   
 2271  Childrens Services                      Food Costs   JS ONLINE GROCERY   
 2284  Childrens Services  Building Repairs & Maintenance   LONDON PLANT HIRE   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 0          2014-04-23          93.0   14381.0  23.0    4.0  2014.0  2.0   
 1          2014-03-04         111.0  600000.0   4.0    3.0  2014.0  1.0   
 2          2014-02-04           6.0   30938.0   4.0    2.0  2014.0  1.0   
 3          2014-02-04           7.0   21876.0   4.0    2.0  2014.0  1.0   
 4          2014-04-15         381.0     306.0  15.0    4.0  2014.0  2.0   
 ...               ...           ...       ...   ...    ...     ...  ...   
 2268       2014-10-21        3276.0       8.0  21.0   10.0  2014.0  4.0   
 2269       2014-07-10        3265.0     321.0  10.0    7.0  2014.0  3.0   
 2270       2014-07-10        3264.0   25983.0  10.0    7.0  2014.0  3.0   
 2271       2014-10-21        3274.0   19196.0  21.0   10.0  2014.0  4.0   
 2284       2014-10-29        3282.0    2308.0  29.0   10.0  2014.0  4.0   
 
      QT_validation final_qr Quarter  
 0           2014Q2   2014Q2      Q2  
 1           2014Q1   2014Q1      Q1  
 2           2014Q1   2014Q1      Q1  
 3           2014Q1   2014Q1      Q1  
 4           2014Q2   2014Q2      Q2  
 ...            ...      ...     ...  
 2268        2014Q4   2014Q4      Q4  
 2269        2014Q3   2014Q3      Q3  
 2270        2014Q3   2014Q3      Q3  
 2271        2014Q4   2014Q4      Q4  
 2284        2014Q4   2014Q4      Q4  
 
 [1297 rows x 13 columns])
('Commercial',
      Service Area             Account Description             Creditor  \
 1489   Commercial  Equipment and Materials Purcha   BRITISH GAS TRADIN   
 1496   Commercial            Legal and Court Fees           CCBCOFFICE   
 1673   Commercial                   Subscriptions           SOLACE LTD   
 1738   Commercial  Private Contractors - Third Pa  WWW.FRIDGELAND.CO.U   
 1748   Commercial  Private Contractors - Third Pa  WWW.TELECARE.ORG.UK   
 1841   Commercial  Private Contractors - Third Pa  EB LOCAL AUTHORITIE   
 1842   Commercial  Private Contractors - Third Pa  EB LOCAL AUTHORITIE   
 2011   Commercial             Travelling Expenses        TRAINLINE.COM   
 2013   Commercial               Telephone Rentals             VODAFONE   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 1489       2014-08-18        1896.0   26275.0  18.0    8.0  2014.0  3.0   
 1496       2014-05-08        1728.0      15.0   8.0    5.0  2014.0  2.0   
 1673       2014-08-18        1895.0     594.0  18.0    8.0  2014.0  3.0   
 1738       2014-08-22        1897.0   71735.0  22.0    8.0  2014.0  3.0   
 1748       2014-08-25        1898.0  100800.0  25.0    8.0  2014.0  3.0   
 1841       2014-09-23        2223.0     450.0  23.0    9.0  2014.0  3.0   
 1842       2014-08-09        2222.0     450.0   9.0    8.0  2014.0  3.0   
 2011       2014-05-09        2221.0    7395.0   9.0    5.0  2014.0  2.0   
 2013       2014-09-26        2224.0      72.0  26.0    9.0  2014.0  3.0   
 
      QT_validation final_qr Quarter  
 1489        2014Q3   2014Q3      Q3  
 1496        2014Q2   2014Q2      Q2  
 1673        2014Q3   2014Q3      Q3  
 1738        2014Q3   2014Q3      Q3  
 1748        2014Q3   2014Q3      Q3  
 1841        2014Q3   2014Q3      Q3  
 1842        2014Q3   2014Q3      Q3  
 2011        2014Q2   2014Q2      Q2  
 2013        2014Q3   2014Q3      Q3  )
('Commissioning',
         Service Area    Account Description             Creditor  \
 2469   Commissioning            IT Services  ADOBE SYSTEMS SOFTW   
 2542   Commissioning  Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 2632   Commissioning  Vehicle Running Costs  BP KELLYS CORNER CO   
 2640   Commissioning            Subsistence         COSTA COFFEE   
 2744   Commissioning            Subsistence       MCDONALDS REST   
 ...              ...                    ...                  ...   
 12322  Commissioning    Travelling Expenses   TRAVEL RESERVATION   
 12323  Commissioning    Travelling Expenses   TRAVEL RESERVATION   
 12324  Commissioning    Travelling Expenses   TRAVEL RESERVATION   
 12325  Commissioning    Travelling Expenses   TRAVEL RESERVATION   
 12326  Commissioning    Travelling Expenses   VIRGINTRAINS.CO.UK   
 
       Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 2469        2014-10-11        3683.0   11433.0  11.0   10.0  2014.0  4.0   
 2542        2014-11-26        3798.0     819.0  26.0   11.0  2014.0  4.0   
 2632        2014-07-11        3649.0       9.0  11.0    7.0  2014.0  3.0   
 2640        2014-11-27        3654.0      27.0  27.0   11.0  2014.0  4.0   
 2744        2014-11-26        3653.0    1145.0  26.0   11.0  2014.0  4.0   
 ...                ...           ...       ...   ...    ...     ...  ...   
 12322       2016-07-25        8880.0  355456.0  25.0    7.0  2016.0  3.0   
 12323       2016-07-25        8899.0  118485.0  25.0    7.0  2016.0  3.0   
 12324       2016-07-25        8900.0  118485.0  25.0    7.0  2016.0  3.0   
 12325       2016-07-25        8901.0  118485.0  25.0    7.0  2016.0  3.0   
 12326       2016-12-04        7740.0   16420.0   4.0   12.0  2016.0  4.0   
 
       QT_validation final_qr Quarter  
 2469         2014Q4   2014Q4      Q4  
 2542         2014Q4   2014Q4      Q4  
 2632         2014Q3   2014Q3      Q3  
 2640         2014Q4   2014Q4      Q4  
 2744         2014Q4   2014Q4      Q4  
 ...             ...      ...     ...  
 12322        2016Q3   2016Q3      Q3  
 12323        2016Q3   2016Q3      Q3  
 12324        2016Q3   2016Q3      Q3  
 12325        2016Q3   2016Q3      Q3  
 12326        2016Q4   2016Q4      Q4  
 
 [400 rows x 13 columns])
('Control Accounts',
          Service Area             Account Description             Creditor  \
 37   Control Accounts  Other Transfer Payments to Soc  Amazon *Mktplce EU-   
 38   Control Accounts  Other Transfer Payments to Soc  Amazon *Mktplce EU-   
 39   Control Accounts  Other Transfer Payments to Soc  Amazon *Mktplce EU-   
 40   Control Accounts  Other Transfer Payments to Soc  Amazon *Mktplce EU-   
 41   Control Accounts  Other Transfer Payments to Soc  Amazon *Mktplce EU-   
 42   Control Accounts  Other Transfer Payments to Soc  Amazon *Mktplce EU-   
 43   Control Accounts  Other Transfer Payments to Soc  Amazon *Mktplce EU-   
 784  Control Accounts          Miscellaneous Expenses   ARGOS RETAIL GROUP   
 
     Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 37        2014-03-04          95.0     415.0   4.0    3.0  2014.0  1.0   
 38        2014-07-04         101.0    8331.0   4.0    7.0  2014.0  3.0   
 39        2014-04-04          99.0     306.0   4.0    4.0  2014.0  2.0   
 40        2014-03-04          96.0    1099.0   4.0    3.0  2014.0  1.0   
 41        2014-04-04          98.0     528.0   4.0    4.0  2014.0  2.0   
 42        2014-04-04         100.0     399.0   4.0    4.0  2014.0  2.0   
 43        2014-03-04          97.0    1599.0   4.0    3.0  2014.0  1.0   
 784       2014-06-13        1019.0    6394.0  13.0    6.0  2014.0  2.0   
 
     QT_validation final_qr Quarter  
 37         2014Q1   2014Q1      Q1  
 38         2014Q3   2014Q3      Q3  
 39         2014Q2   2014Q2      Q2  
 40         2014Q1   2014Q1      Q1  
 41         2014Q2   2014Q2      Q2  
 42         2014Q2   2014Q2      Q2  
 43         2014Q1   2014Q1      Q1  
 784        2014Q2   2014Q2      Q2  )
('Customer Support Group',
                  Service Area    Account Description                 Creditor  \
 2622   Customer Support Group  Books-CDs-Audio-Video                    B R E   
 2693   Customer Support Group   Legal and Court Fees       HMCOURTS-SERVICE.G   
 2694   Customer Support Group   Legal and Court Fees       HMCOURTS-SERVICE.G   
 2695   Customer Support Group   Legal and Court Fees       HMCOURTS-SERVICE.G   
 2696   Customer Support Group   Legal and Court Fees       HMCOURTS-SERVICE.G   
 ...                       ...                    ...                      ...   
 12362  Customer Support Group          Subscriptions         SURVEYMONKEY.COM   
 12363  Customer Support Group      Telephone Rentals             VODAFONE LTD   
 12364  Customer Support Group               Training             WWW.CIPS.ORG   
 12365  Customer Support Group         Ttl IT & Comms  PAYPAL  - REDACTED DATA   
 12366  Customer Support Group         Ttl IT & Comms      PAYPAL  CLARIONCOMM   
 
       Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 2622        2014-10-11        3895.0      36.0  11.0   10.0  2014.0  4.0   
 2693        2014-06-11        3923.0    4707.0  11.0    6.0  2014.0  2.0   
 2694        2014-04-11        3922.0     405.0  11.0    4.0  2014.0  2.0   
 2695        2014-11-18        3920.0    3567.0  18.0   11.0  2014.0  4.0   
 2696        2014-11-18        3921.0      60.0  18.0   11.0  2014.0  4.0   
 ...                ...           ...       ...   ...    ...     ...  ...   
 12362       2017-01-17       11247.0   30000.0  17.0    1.0  2017.0  1.0   
 12363       2016-05-18        8107.0   19608.0  18.0    5.0  2016.0  2.0   
 12364       2016-03-23        7570.0    9792.0  23.0    3.0  2016.0  1.0   
 12365       2016-07-11       10150.0   16995.0  11.0    7.0  2016.0  3.0   
 12366       2016-07-11       10151.0    6500.0  11.0    7.0  2016.0  3.0   
 
       QT_validation final_qr Quarter  
 2622         2014Q4   2014Q4      Q4  
 2693         2014Q2   2014Q2      Q2  
 2694         2014Q2   2014Q2      Q2  
 2695         2014Q4   2014Q4      Q4  
 2696         2014Q4   2014Q4      Q4  
 ...             ...      ...     ...  
 12362        2017Q1   2017Q1      Q1  
 12363        2016Q2   2016Q2      Q2  
 12364        2016Q1   2016Q1      Q1  
 12365        2016Q3   2016Q3      Q3  
 12366        2016Q3   2016Q3      Q3  
 
 [117 rows x 13 columns])
('Deputy Chief Operating Officer',
                         Service Area             Account Description  \
 80    Deputy Chief Operating Officer  Cleaning and domestic material   
 114   Deputy Chief Operating Officer             Travelling Expenses   
 168   Deputy Chief Operating Officer  Equipment and Materials Purcha   
 187   Deputy Chief Operating Officer  Other Indirect Employee Expens   
 213   Deputy Chief Operating Officer                     Subsistence   
 ...                              ...                             ...   
 2419  Deputy Chief Operating Officer             Travelling Expenses   
 2420  Deputy Chief Operating Officer             Travelling Expenses   
 2421  Deputy Chief Operating Officer             Travelling Expenses   
 2423  Deputy Chief Operating Officer                     Subsistence   
 2458  Deputy Chief Operating Officer                     Advertising   
 
                  Creditor Transaction Date  JV Reference  JV Value  date  \
 80     BLAKES DRY CLEANER       2014-04-23         109.0      10.0  23.0   
 114    FIRST CAPITAL CONN       2014-04-28         110.0      10.0  28.0   
 168     KAGI 1-51-42-5858       2014-04-25         155.0    3166.0  25.0   
 187   MIDDLESEX UNIVERSIT       2014-09-04         107.0     578.0   4.0   
 213       PINE RIDGE GOLF       2014-11-04         108.0     824.0   4.0   
 ...                   ...              ...           ...       ...   ...   
 2419    TFL CC/LEZ CHARGE       2014-10-14        3290.0     115.0  14.0   
 2420    TFL CC/LEZ CHARGE       2014-08-10        3288.0     115.0  10.0   
 2421    TFL CC/LEZ CHARGE       2014-10-20        3291.0     115.0  20.0   
 2423      THE CORNER CAFE       2014-10-20        3299.0      65.0  20.0   
 2458  WWW.GETTYIMAGES.COM       2014-10-20        3337.0    5825.0  20.0   
 
       month    year   QT QT_validation final_qr Quarter  
 80      4.0  2014.0  2.0        2014Q2   2014Q2      Q2  
 114     4.0  2014.0  2.0        2014Q2   2014Q2      Q2  
 168     4.0  2014.0  2.0        2014Q2   2014Q2      Q2  
 187     9.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 213    11.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 ...     ...     ...  ...           ...      ...     ...  
 2419   10.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2420    8.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 2421   10.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2423   10.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 2458   10.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 
 [113 rows x 13 columns])
('Education',
      Service Area             Account Description             Creditor  \
 1461    Education  Equipment and Materials Purcha            Amazon EU   
 1497    Education  Equipment and Materials Purcha           CE-CES LTD   
 1500    Education  Equipment and Materials Purcha        COMPLETE CARE   
 1529    Education  Cleaning and domestic material  FIRSTCHOICE CATERIN   
 1535    Education  Equipment and Materials Purcha     GRANADA LEARNING   
 ...           ...                             ...                  ...   
 2437    Education  Equipment and Materials Purcha         WAITROSE 124   
 2438    Education  Equipment and Materials Purcha         WAITROSE 124   
 2439    Education                      Food Costs         WAITROSE 124   
 2442    Education  Equipment and Materials Purcha   WMF UNITED KINGDOM   
 2453    Education           Books-CDs-Audio-Video       WWW.ASE.ORG.UK   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 1461       2014-08-22        1922.0    6662.0  22.0    8.0  2014.0  3.0   
 1497       2014-12-08        1629.0     500.0   8.0   12.0  2014.0  4.0   
 1500       2014-08-08        1729.0    5022.0   8.0    8.0  2014.0  3.0   
 1529       2014-01-09        1634.0    2469.0   9.0    1.0  2014.0  1.0   
 1535       2014-08-22        1923.0   24792.0  22.0    8.0  2014.0  3.0   
 ...               ...           ...       ...   ...    ...     ...  ...   
 2437       2014-10-30        3132.0    3395.0  30.0   10.0  2014.0  4.0   
 2438       2014-03-10        3129.0    2635.0  10.0    3.0  2014.0  1.0   
 2439       2014-07-10        3117.0      12.0  10.0    7.0  2014.0  3.0   
 2442       2014-10-28        3321.0    1488.0  28.0   10.0  2014.0  4.0   
 2453       2014-02-10        3468.0     221.0  10.0    2.0  2014.0  1.0   
 
      QT_validation final_qr Quarter  
 1461        2014Q3   2014Q3      Q3  
 1497        2014Q4   2014Q4      Q4  
 1500        2014Q3   2014Q3      Q3  
 1529        2014Q1   2014Q1      Q1  
 1535        2014Q3   2014Q3      Q3  
 ...            ...      ...     ...  
 2437        2014Q4   2014Q4      Q4  
 2438        2014Q1   2014Q1      Q1  
 2439        2014Q3   2014Q3      Q3  
 2442        2014Q4   2014Q4      Q4  
 2453        2014Q1   2014Q1      Q1  
 
 [95 rows x 13 columns])
('Family Services',
          Service Area             Account Description             Creditor  \
 1422  Family Services  Private Contractors - Third Pa   ACTIVITYBOOKER.NET   
 1423  Family Services             Clothing - Uniforms        ADIDAS UK LTD   
 1424  Family Services             Clothing - Uniforms        ADIDAS UK LTD   
 1425  Family Services  Clothing - Protective Clothing            ALEXANDRA   
 1427  Family Services           Books-CDs-Audio-Video  APEchildrens-food-t   
 ...               ...                             ...                  ...   
 2456  Family Services  Equipment and Materials Purcha     WWW.CARPCO.CO.UK   
 2460  Family Services                      Food Costs  WWW.MILKANDMORE.CO.   
 2461  Family Services  Equipment and Materials Purcha      WWW.NISBETS.COM   
 2462  Family Services  Equipment and Materials Purcha        WWW.QVCUK.COM   
 2467  Family Services  Private Contractors - Third Pa  amazonpayments.co.u   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 1422       2014-08-13        1777.0    3825.0  13.0    8.0  2014.0  3.0   
 1423       2014-08-25        1782.0      48.0  25.0    8.0  2014.0  3.0   
 1424       2014-08-25        1781.0     595.0  25.0    8.0  2014.0  3.0   
 1425       2014-08-13        1859.0     116.0  13.0    8.0  2014.0  3.0   
 1427       2014-08-15        1622.0     164.0  15.0    8.0  2014.0  3.0   
 ...               ...           ...       ...   ...    ...     ...  ...   
 2456       2014-10-31        3467.0    4321.0  31.0   10.0  2014.0  4.0   
 2460       2014-10-23        3224.0     352.0  23.0   10.0  2014.0  4.0   
 2461       2014-10-27        3287.0   32527.0  27.0   10.0  2014.0  4.0   
 2462       2014-10-31        3242.0    2795.0  31.0   10.0  2014.0  4.0   
 2467       2014-10-30        3417.0    5209.0  30.0   10.0  2014.0  4.0   
 
      QT_validation final_qr Quarter  
 1422        2014Q3   2014Q3      Q3  
 1423        2014Q3   2014Q3      Q3  
 1424        2014Q3   2014Q3      Q3  
 1425        2014Q3   2014Q3      Q3  
 1427        2014Q3   2014Q3      Q3  
 ...            ...      ...     ...  
 2456        2014Q4   2014Q4      Q4  
 2460        2014Q4   2014Q4      Q4  
 2461        2014Q4   2014Q4      Q4  
 2462        2014Q4   2014Q4      Q4  
 2467        2014Q4   2014Q4      Q4  
 
 [770 rows x 13 columns])
('Governance',
      Service Area             Account Description             Creditor  \
 77     Governance                  Other Services   BETTER LIFE HEALTH   
 78     Governance                  Other Services   BETTER LIFE HEALTH   
 747    Governance  Equipment and Materials Purcha   TRAVELODGE WEBSITE   
 991    Governance  Building Repairs & Maintenance      SCREWFIX DIRECT   
 1056   Governance           Professional Services    WESTMINSTER FORUM   
 2029   Governance                      Stationery  WWW.CARTRIDGESAVE.C   
 2030   Governance                      Stationery  WWW.CARTRIDGESAVE.C   
 2058   Governance  Equipment and Materials Purcha         ARGOS DIRECT   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 77         2014-04-24         286.0  638820.0  24.0    4.0  2014.0  2.0   
 78         2014-04-24         287.0  638820.0  24.0    4.0  2014.0  2.0   
 747        2014-05-21         496.0     160.0  21.0    5.0  2014.0  2.0   
 991        2014-06-24        1196.0     752.0  24.0    6.0  2014.0  2.0   
 1056       2014-01-07        1198.0     252.0   7.0    1.0  2014.0  1.0   
 2029       2014-12-09        2153.0   48003.0   9.0   12.0  2014.0  4.0   
 2030       2014-09-24        2155.0   44493.0  24.0    9.0  2014.0  3.0   
 2058       2014-10-22        3397.0    5394.0  22.0   10.0  2014.0  4.0   
 
      QT_validation final_qr Quarter  
 77          2014Q2   2014Q2      Q2  
 78          2014Q2   2014Q2      Q2  
 747         2014Q2   2014Q2      Q2  
 991         2014Q2   2014Q2      Q2  
 1056        2014Q1   2014Q1      Q1  
 2029        2014Q4   2014Q4      Q4  
 2030        2014Q3   2014Q3      Q3  
 2058        2014Q4   2014Q4      Q4  )
('Grand Total',
       Service Area Account Description Creditor Transaction Date  \
 12588  Grand Total                 NaN      NaN              NaT   
 
        JV Reference    JV Value  date  month  year  QT QT_validation final_qr  \
 12588           NaN  47104401.0   NaN    NaN   NaN NaN           NaT      NaT   
 
       Quarter  
 12588      Qn  )
('HRA',
       Service Area  Account Description       Creditor Transaction Date  \
 12367          HRA  Conference Expenses  HFE SIGNS LTD       2016-11-22   
 
        JV Reference  JV Value  date  month    year   QT QT_validation  \
 12367       10341.0   28994.0  22.0   11.0  2016.0  4.0        2016Q4   
 
       final_qr Quarter  
 12367   2016Q4      Q4  )
('Internal Audit & CAFT',
                Service Area             Account Description  \
 175   Internal Audit & CAFT            Legal and Court Fees   
 860   Internal Audit & CAFT  Equipment and Materials Purcha   
 1162  Internal Audit & CAFT            Legal and Court Fees   
 1178  Internal Audit & CAFT  Equipment and Materials Purcha   
 1194  Internal Audit & CAFT            Legal and Court Fees   
 1481  Internal Audit & CAFT            Legal and Court Fees   
 1519  Internal Audit & CAFT  Equipment and Materials Purcha   
 1887  Internal Audit & CAFT             Travelling Expenses   
 1888  Internal Audit & CAFT             Travelling Expenses   
 2010  Internal Audit & CAFT             Travelling Expenses   
 2205  Internal Audit & CAFT  Private Contractors - Third Pa   
 
                  Creditor Transaction Date  JV Reference  JV Value  date  \
 175   LBB CIVIC CENTRE PA       2014-10-04         184.0       4.0   4.0   
 860     CANFORD AUDIO PLC       2014-06-19        1195.0    4032.0  19.0   
 1162  BARNET REGISTER OFF       2014-07-14        1417.0      30.0  14.0   
 1178            D H C LTD       2014-07-25        1617.0    3708.0  25.0   
 1194  GRO HM PASSPORT OFF       2014-07-16        1418.0     234.0  16.0   
 1481  BARNET REGISTER OFF       2014-04-08        1930.0      15.0   8.0   
 1519            D H C LTD       2014-04-08        1931.0    5607.0   8.0   
 1887   LUL TICKET OFFICE.       2014-09-24        2248.0     114.0  24.0   
 1888   LUL TICKET OFFICE.       2014-01-10        2249.0      17.0  10.0   
 2010   TL RAILWAY TICKETS       2014-09-19        2247.0      20.0  19.0   
 2205  EB TENANCY FRAUD FO       2014-10-30        3504.0      99.0  30.0   
 
       month    year   QT QT_validation final_qr Quarter  
 175    10.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 860     6.0  2014.0  2.0        2014Q2   2014Q2      Q2  
 1162    7.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 1178    7.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 1194    7.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 1481    4.0  2014.0  2.0        2014Q2   2014Q2      Q2  
 1519    4.0  2014.0  2.0        2014Q2   2014Q2      Q2  
 1887    9.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 1888    1.0  2014.0  1.0        2014Q1   2014Q1      Q1  
 2010    9.0  2014.0  3.0        2014Q3   2014Q3      Q3  
 2205   10.0  2014.0  4.0        2014Q4   2014Q4      Q4  )
('NSCSO',
      Service Area             Account Description             Creditor  \
 251         NSCSO  Private Contractors - Third Pa         SARACENS LTD   
 1386        NSCSO           Professional Services    TVLICENSING.CO.UK   
 1389        NSCSO    Consumable Catering Supplies  VOUCHER EXPRESS B2B   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 251        2014-04-17         285.0      10.0  17.0    4.0  2014.0  2.0   
 1386       2014-07-25        1449.0    1455.0  25.0    7.0  2014.0  3.0   
 1389       2014-07-18        1448.0     300.0  18.0    7.0  2014.0  3.0   
 
      QT_validation final_qr Quarter  
 251         2014Q2   2014Q2      Q2  
 1386        2014Q3   2014Q3      Q3  
 1389        2014Q3   2014Q3      Q3  )
('Parking & Infrastructure',
                    Service Area             Account Description  \
 2526   Parking & Infrastructure  Equipment and Materials Purcha   
 2553   Parking & Infrastructure           Books-CDs-Audio-Video   
 3820   Parking & Infrastructure  Equipment and Materials Purcha   
 4304   Parking & Infrastructure  Equipment and Materials Purcha   
 12368  Parking & Infrastructure  Equipment and Materials Purcha   
 12369  Parking & Infrastructure  Equipment and Materials Purcha   
 12370  Parking & Infrastructure  Equipment and Materials Purcha   
 12371  Parking & Infrastructure  Equipment and Materials Purcha   
 12372  Parking & Infrastructure  Equipment and Materials Purcha   
 12373  Parking & Infrastructure  Equipment and Materials Purcha   
 12374  Parking & Infrastructure          Miscellaneous Expenses   
 12375  Parking & Infrastructure          Miscellaneous Expenses   
 
                      Creditor Transaction Date  JV Reference  JV Value  date  \
 2526                Amazon EU       2014-06-11        3531.0     418.0  11.0   
 2553      Amazon Mktplce EU-U       2014-11-25        3541.0    5102.0  25.0   
 3820         Amazon UK Retail       2015-06-02        4827.0    2843.0   2.0   
 4304                AMAZON EU       2015-01-10        5497.0   15967.0  10.0   
 12368               AMAZON EU       2017-03-23       11810.0    4497.0  23.0   
 12369   AMAZON UK MARKETPLACE       2017-02-15       11451.0    1178.0  15.0   
 12370   AMAZON UK MARKETPLACE       2017-02-15       11453.0    6794.0  15.0   
 12371   AMAZON UK MARKETPLACE       2017-03-23       11811.0   10999.0  23.0   
 12372   AMAZON UK MARKETPLACE       2016-07-29        8963.0    7625.0  29.0   
 12373  WWW.MIDLANDPALLETTRUCK       2016-12-16       10668.0   79500.0  16.0   
 12374   NOCHEX. BPH POSTCODES       2016-06-15        8392.0   50000.0  15.0   
 12375      WWW.OPUSENERGY.COM       2016-10-10        9772.0  277325.0  10.0   
 
        month    year   QT QT_validation final_qr Quarter  
 2526     6.0  2014.0  2.0        2014Q2   2014Q2      Q2  
 2553    11.0  2014.0  4.0        2014Q4   2014Q4      Q4  
 3820     6.0  2015.0  2.0        2015Q2   2015Q2      Q2  
 4304     1.0  2015.0  1.0        2015Q1   2015Q1      Q1  
 12368    3.0  2017.0  1.0        2017Q1   2017Q1      Q1  
 12369    2.0  2017.0  1.0        2017Q1   2017Q1      Q1  
 12370    2.0  2017.0  1.0        2017Q1   2017Q1      Q1  
 12371    3.0  2017.0  1.0        2017Q1   2017Q1      Q1  
 12372    7.0  2016.0  3.0        2016Q3   2016Q3      Q3  
 12373   12.0  2016.0  4.0        2016Q4   2016Q4      Q4  
 12374    6.0  2016.0  2.0        2016Q2   2016Q2      Q2  
 12375   10.0  2016.0  4.0        2016Q4   2016Q4      Q4  )
('Public Health',
         Service Area             Account Description             Creditor  \
 150    Public Health  Equipment and Materials Purcha      INSPIRED FRAMES   
 833    Public Health           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 12376  Public Health  Equipment and Materials Purcha   VALUE PRODUCTS LTD   
 
       Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 150         2014-04-25         105.0    1095.0  25.0    4.0  2014.0  2.0   
 833         2014-03-06        1192.0     133.0   6.0    3.0  2014.0  1.0   
 12376       2016-06-22        8486.0     455.0  22.0    6.0  2016.0  2.0   
 
       QT_validation final_qr Quarter  
 150          2014Q2   2014Q2      Q2  
 833          2014Q1   2014Q1      Q1  
 12376        2016Q2   2016Q2      Q2  )
('Regional Enterprise',
               Service Area             Account Description  \
 3613   Regional Enterprise  Equipment and Materials Purcha   
 5872   Regional Enterprise           Professional Services   
 12377  Regional Enterprise                Consultants Fees   
 12378  Regional Enterprise                  Other Services   
 12379  Regional Enterprise                  Other Services   
 12380  Regional Enterprise                   Subscriptions   
 
                     Creditor Transaction Date  JV Reference  JV Value  date  \
 3613     WWW.COMPANYWATCH.NE       2015-01-22        4502.0      60.0  22.0   
 5872   J W RUDDOCK &SONS LTD       2015-01-12        6227.0  164500.0  12.0   
 12377   WWW.COMPANYWATCH.NET       2016-03-14        7443.0    6000.0  14.0   
 12378     HMCOURTS-SERVICE.G       2017-12-01       11246.0   22600.0   1.0   
 12379     HMCOURTS-SERVICE.G       2016-12-20       10735.0   22600.0  20.0   
 12380        COMPANIES HOUSE       2016-11-08        9079.0    1200.0   8.0   
 
        month    year   QT QT_validation final_qr Quarter  
 3613     1.0  2015.0  1.0        2015Q1   2015Q1      Q1  
 5872     1.0  2015.0  1.0        2015Q1   2015Q1      Q1  
 12377    3.0  2016.0  1.0        2016Q1   2016Q1      Q1  
 12378   12.0  2017.0  4.0        2017Q4   2017Q4      Q4  
 12379   12.0  2016.0  4.0        2016Q4   2016Q4      Q4  
 12380   11.0  2016.0  4.0        2016Q4   2016Q4      Q4  )
('Strategic Commissioning Board',
                       Service Area  Account Description            Creditor  \
 928  Strategic Commissioning Board  Travelling Expenses  JURYS MANCHESTER I   
 
     Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 928       2014-06-26        1197.0     244.0  26.0    6.0  2014.0  2.0   
 
     QT_validation final_qr Quarter  
 928        2014Q2   2014Q2      Q2  )
('Street Scene',
       Service Area             Account Description             Creditor  \
 59    Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 60    Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 61    Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 111   Street Scene            Legal and Court Fees    ENVIROMENT AGENCY   
 319   Street Scene                     Advertising  WWW.BLUESKY-WORLD.C   
 821   Street Scene  Equipment and Materials Purcha            Amazon EU   
 822   Street Scene  Equipment and Materials Purcha            Amazon EU   
 828   Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 829   Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 952   Street Scene             Travelling Expenses     OYSTER AUTOTOPUP   
 1061  Street Scene                     Advertising  WWW.BLUESKY-WORLD.C   
 1118  Street Scene  Equipment and Materials Purcha            Amazon EU   
 1133  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1134  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1135  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1136  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1137  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1138  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1459  Street Scene  Equipment and Materials Purcha            Amazon EU   
 1460  Street Scene  Equipment and Materials Purcha            Amazon EU   
 1469  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1478  Street Scene  Equipment and Materials Purcha  Amazon Mktplce EU-U   
 1479  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1804  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1805  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 1850  Street Scene           Vehicle Running Costs        GRANGE BARNET   
 1909  Street Scene             Travelling Expenses     OYSTER AUTOTOPUP   
 2118  Street Scene  Equipment and Materials Purcha            Amazon EU   
 2119  Street Scene  Equipment and Materials Purcha            Amazon EU   
 2120  Street Scene  Equipment and Materials Purcha            Amazon EU   
 2147  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 2148  Street Scene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 2302  Street Scene             Travelling Expenses     OYSTER AUTOTOPUP   
 2328  Street Scene           Vehicle Running Costs  POST OFFICE COUNTER   
 2329  Street Scene           Vehicle Running Costs  POST OFFICE COUNTER   
 2429  Street Scene             Travelling Expenses   UK PARKING CONTROL   
 2432  Street Scene                        Training        VOSA-TRAINING   
 2465  Street Scene  Equipment and Materials Purcha  WWW.STANTONHOPE.CO.   
 2466  Street Scene  Equipment and Materials Purcha  WWW.STANTONHOPE.CO.   
 
      Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 59         2014-04-21           4.0    6372.0  21.0    4.0  2014.0  2.0   
 60         2014-04-16           2.0    1198.0  16.0    4.0  2014.0  2.0   
 61         2014-01-05           5.0      42.0   5.0    1.0  2014.0  1.0   
 111        2014-11-04           1.0     100.0   4.0   11.0  2014.0  4.0   
 319        2014-04-17           3.0    1176.0  17.0    4.0  2014.0  2.0   
 821        2014-06-06         945.0    1043.0   6.0    6.0  2014.0  2.0   
 822        2014-09-06         946.0    4173.0   6.0    9.0  2014.0  3.0   
 828        2014-06-13         947.0     595.0  13.0    6.0  2014.0  2.0   
 829        2014-06-19         949.0    8332.0  19.0    6.0  2014.0  2.0   
 952        2014-06-30         950.0      20.0  30.0    6.0  2014.0  2.0   
 1061       2014-06-19         948.0    1176.0  19.0    6.0  2014.0  2.0   
 1118       2014-01-08        1263.0    1179.0   8.0    1.0  2014.0  1.0   
 1133       2014-07-28        1261.0    1799.0  28.0    7.0  2014.0  3.0   
 1134       2014-07-28        1262.0     108.0  28.0    7.0  2014.0  3.0   
 1135       2014-07-18        1260.0     795.0  18.0    7.0  2014.0  3.0   
 1136       2014-07-18        1258.0    4367.0  18.0    7.0  2014.0  3.0   
 1137       2014-07-17        1257.0    2682.0  17.0    7.0  2014.0  3.0   
 1138       2014-07-18        1259.0     299.0  18.0    7.0  2014.0  3.0   
 1459       2014-08-15        1625.0   12329.0  15.0    8.0  2014.0  3.0   
 1460       2014-11-08        1624.0     607.0   8.0   11.0  2014.0  4.0   
 1469       2014-07-08        1623.0      65.0   8.0    7.0  2014.0  3.0   
 1478       2014-08-21        1626.0     148.0  21.0    8.0  2014.0  3.0   
 1479       2014-08-29        1627.0    1268.0  29.0    8.0  2014.0  3.0   
 1804       2014-09-26        1956.0    1118.0  26.0    9.0  2014.0  3.0   
 1805       2014-12-09        1954.0    6897.0   9.0   12.0  2014.0  4.0   
 1850       2014-11-09        2250.0      72.0   9.0   11.0  2014.0  4.0   
 1909       2014-09-15        1955.0      20.0  15.0    9.0  2014.0  3.0   
 2118       2014-10-24        3111.0     193.0  24.0   10.0  2014.0  4.0   
 2119       2014-10-27        3113.0     193.0  27.0   10.0  2014.0  4.0   
 2120       2014-03-10        3107.0   12329.0  10.0    3.0  2014.0  1.0   
 2147       2014-10-28        3115.0     962.0  28.0   10.0  2014.0  4.0   
 2148       2014-10-13        3110.0     533.0  13.0   10.0  2014.0  4.0   
 2302       2014-10-27        3112.0      20.0  27.0   10.0  2014.0  4.0   
 2328       2014-10-29        3507.0     400.0  29.0   10.0  2014.0  4.0   
 2329       2014-10-29        3506.0    2875.0  29.0   10.0  2014.0  4.0   
 2429       2014-06-10        3108.0     615.0  10.0    6.0  2014.0  2.0   
 2432       2014-10-23        3505.0    1518.0  23.0   10.0  2014.0  4.0   
 2465       2014-10-28        3114.0     408.0  28.0   10.0  2014.0  4.0   
 2466       2014-10-13        3109.0   14514.0  13.0   10.0  2014.0  4.0   
 
      QT_validation final_qr Quarter  
 59          2014Q2   2014Q2      Q2  
 60          2014Q2   2014Q2      Q2  
 61          2014Q1   2014Q1      Q1  
 111         2014Q4   2014Q4      Q4  
 319         2014Q2   2014Q2      Q2  
 821         2014Q2   2014Q2      Q2  
 822         2014Q3   2014Q3      Q3  
 828         2014Q2   2014Q2      Q2  
 829         2014Q2   2014Q2      Q2  
 952         2014Q2   2014Q2      Q2  
 1061        2014Q2   2014Q2      Q2  
 1118        2014Q1   2014Q1      Q1  
 1133        2014Q3   2014Q3      Q3  
 1134        2014Q3   2014Q3      Q3  
 1135        2014Q3   2014Q3      Q3  
 1136        2014Q3   2014Q3      Q3  
 1137        2014Q3   2014Q3      Q3  
 1138        2014Q3   2014Q3      Q3  
 1459        2014Q3   2014Q3      Q3  
 1460        2014Q4   2014Q4      Q4  
 1469        2014Q3   2014Q3      Q3  
 1478        2014Q3   2014Q3      Q3  
 1479        2014Q3   2014Q3      Q3  
 1804        2014Q3   2014Q3      Q3  
 1805        2014Q4   2014Q4      Q4  
 1850        2014Q4   2014Q4      Q4  
 1909        2014Q3   2014Q3      Q3  
 2118        2014Q4   2014Q4      Q4  
 2119        2014Q4   2014Q4      Q4  
 2120        2014Q1   2014Q1      Q1  
 2147        2014Q4   2014Q4      Q4  
 2148        2014Q4   2014Q4      Q4  
 2302        2014Q4   2014Q4      Q4  
 2328        2014Q4   2014Q4      Q4  
 2329        2014Q4   2014Q4      Q4  
 2429        2014Q2   2014Q2      Q2  
 2432        2014Q4   2014Q4      Q4  
 2465        2014Q4   2014Q4      Q4  
 2466        2014Q4   2014Q4      Q4  )
('Streetscene',
       Service Area             Account Description             Creditor  \
 2523   Streetscene  Equipment and Materials Purcha            Amazon EU   
 2524   Streetscene  Equipment and Materials Purcha            Amazon EU   
 2550   Streetscene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 2551   Streetscene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 2552   Streetscene           Books-CDs-Audio-Video  Amazon Mktplce EU-U   
 ...            ...                             ...                  ...   
 12583  Streetscene           Vehicle Running Costs      WWW.DVLA.GOV.UK   
 12584  Streetscene           Vehicle Running Costs      WWW.DVLA.GOV.UK   
 12585  Streetscene           Vehicle Running Costs      WWW.DVLA.GOV.UK   
 12586  Streetscene           Vehicle Running Costs      WWW.DVLA.GOV.UK   
 12587  Streetscene           Vehicle Running Costs   WWW.MOT-TESTING-CP   
 
       Transaction Date  JV Reference  JV Value  date  month    year   QT  \
 2523        2014-06-11        3532.0     108.0  11.0    6.0  2014.0  2.0   
 2524        2014-11-21        3537.0    9466.0  21.0   11.0  2014.0  4.0   
 2550        2014-11-24        3539.0     358.0  24.0   11.0  2014.0  4.0   
 2551        2014-07-11        3533.0     212.0  11.0    7.0  2014.0  3.0   
 2552        2014-11-24        3538.0      50.0  24.0   11.0  2014.0  4.0   
 ...                ...           ...       ...   ...    ...     ...  ...   
 12583       2016-08-23        9211.0   23250.0  23.0    8.0  2016.0  3.0   
 12584       2016-08-23        9212.0   23250.0  23.0    8.0  2016.0  3.0   
 12585       2016-08-23        9213.0   16750.0  23.0    8.0  2016.0  3.0   
 12586       2016-08-25        9248.0   23250.0  25.0    8.0  2016.0  3.0   
 12587       2016-11-25       10384.0    6800.0  25.0   11.0  2016.0  4.0   
 
       QT_validation final_qr Quarter  
 2523         2014Q2   2014Q2      Q2  
 2524         2014Q4   2014Q4      Q4  
 2550         2014Q4   2014Q4      Q4  
 2551         2014Q3   2014Q3      Q3  
 2552         2014Q4   2014Q4      Q4  
 ...             ...      ...     ...  
 12583        2016Q3   2016Q3      Q3  
 12584        2016Q3   2016Q3      Q3  
 12585        2016Q3   2016Q3      Q3  
 12586        2016Q3   2016Q3      Q3  
 12587        2016Q4   2016Q4      Q4  
 
 [420 rows x 13 columns])

need to group data for total,avg,most common value of the transaction by service area, then accounts (year,quater)¶

In [38]:
def summary_table(data,group,value):
    """
    arg: data--> dataframe given
    arg: group--> list of columns to be group by
    arg: value--> column where stats are need to be calcualted 
    
    func: grouping the df by the columns mentioned
    
    return: dataframe confined by the parameters passed by
    """
    output=df.groupby(group)[value].agg(['count', 'mean', 'median','sum']).reset_index()
    print("created a dataframe with columns grouped by: {}\n values are aggregated by: {}".format(group,value))
    
    return output
In [39]:
sa_year_qrt=summary_table(df,list(('Service Area', 'year', 'Quarter')),'JV Value')
sa_year=summary_table(df,list(('Service Area', 'year')),'JV Value')

created a dataframe with columns grouped by: ['Service Area', 'year', 'Quarter']
 values are aggregated by: JV Value
created a dataframe with columns grouped by: ['Service Area', 'year']
 values are aggregated by: JV Value
In [40]:
ad_year_qt=summary_table(df,list(('Account Description', 'year', 'Quarter')),'JV Value')
ad_year=summary_table(df,list(('Account Description', 'year')),'JV Value')

created a dataframe with columns grouped by: ['Account Description', 'year', 'Quarter']
 values are aggregated by: JV Value
created a dataframe with columns grouped by: ['Account Description', 'year']
 values are aggregated by: JV Value
In [41]:
#summary view of the transactions for each Service Area
In [42]:
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

Question 1 part 2¶

summary table -I¶

In [43]:
#displaying the created dataframe through groupby function for the second part of the question
sa_year_qrt
Out[43]:
Service Area year Quarter count mean median sum
0 Adults and Communities 2014.0 Q1 2 225.500000 225.5 451.0
1 Adults and Communities 2014.0 Q2 15 4793.400000 207.0 71901.0
2 Adults and Communities 2014.0 Q3 11 228.545455 124.0 2514.0
3 Adults and Communities 2014.0 Q4 14 3502.000000 198.0 49028.0
4 Adults and Communities 2015.0 Q1 7 1044.285714 268.0 7310.0
5 Adults and Communities 2015.0 Q2 10 14753.000000 205.0 147530.0
6 Adults and Communities 2015.0 Q3 19 3990.526316 164.0 75820.0
7 Adults and Communities 2015.0 Q4 17 1470.000000 1667.0 24990.0
8 Adults and Communities 2016.0 Q1 23 8437.130435 3247.0 194054.0
9 Adults and Communities 2016.0 Q2 38 19754.342105 2673.5 750665.0
10 Adults and Communities 2016.0 Q3 33 14560.393939 2504.0 480493.0
11 Adults and Communities 2016.0 Q4 36 18543.694444 2295.0 667573.0
12 Adults and Communities 2017.0 Q1 37 13530.297297 2000.0 500621.0
13 Adults and Communities 2017.0 Q2 6 2655.666667 2450.0 15934.0
14 Adults and Communities 2017.0 Q3 7 6912.428571 1999.0 48387.0
15 Adults and Communities 2017.0 Q4 3 119837.000000 1613.0 359511.0
16 Assurance 2014.0 Q2 1 8994.000000 8994.0 8994.0
17 Assurance 2014.0 Q4 2 59.000000 59.0 118.0
18 Assurance 2015.0 Q1 12 1698.250000 267.0 20379.0
19 Assurance 2015.0 Q2 42 3856.142857 320.5 161958.0
20 Assurance 2015.0 Q3 37 823.567568 234.0 30472.0
21 Assurance 2015.0 Q4 34 1505.441176 289.0 51185.0
22 Assurance 2016.0 Q1 38 13429.763158 1363.5 510331.0
23 Assurance 2016.0 Q2 56 8318.464286 1150.0 465834.0
24 Assurance 2016.0 Q3 44 7523.204545 1275.5 331021.0
25 Assurance 2016.0 Q4 40 3302.650000 750.0 132106.0
26 Assurance 2017.0 Q1 31 3148.225806 1271.0 97595.0
27 Assurance 2017.0 Q2 1 12687.000000 12687.0 12687.0
28 Assurance 2017.0 Q3 6 1122.500000 850.5 6735.0
29 CSG Managed Budget 2014.0 Q1 4 879.250000 636.0 3517.0
30 CSG Managed Budget 2014.0 Q2 15 195759.666667 4788.0 2936395.0
31 CSG Managed Budget 2014.0 Q3 10 132845.300000 65913.0 1328453.0
32 CSG Managed Budget 2014.0 Q4 7 3205.428571 4499.0 22438.0
33 Children's Education & Skills 2014.0 Q1 3 18812.000000 18079.0 56436.0
34 Children's Education & Skills 2014.0 Q2 9 2535.777778 1721.0 22822.0
35 Children's Education & Skills 2014.0 Q3 6 8147.666667 485.0 48886.0
36 Children's Education & Skills 2014.0 Q4 54 5550.037037 1385.0 299702.0
37 Children's Education & Skills 2015.0 Q1 119 3841.042017 1704.0 457084.0
38 Children's Education & Skills 2015.0 Q2 135 5364.229630 1598.0 724171.0
39 Children's Education & Skills 2015.0 Q3 129 5482.612403 1385.0 707257.0
40 Children's Education & Skills 2015.0 Q4 124 6436.637097 1385.0 798143.0
41 Children's Education & Skills 2016.0 Q1 41 7657.195122 498.0 313945.0
42 Children's Education & Skills 2016.0 Q2 16 11424.375000 7136.5 182790.0
43 Children's Education & Skills 2016.0 Q3 21 19634.809524 11400.0 412331.0
44 Children's Education & Skills 2016.0 Q4 8 11722.375000 3087.0 93779.0
45 Children's Education & Skills 2017.0 Q1 2 5805.000000 5805.0 11610.0
46 Children's Family Services 2014.0 Q1 46 6125.695652 413.0 281782.0
47 Children's Family Services 2014.0 Q2 65 2881.800000 506.0 187317.0
48 Children's Family Services 2014.0 Q3 38 5390.342105 775.0 204833.0
49 Children's Family Services 2014.0 Q4 448 3135.962054 337.5 1404911.0
50 Children's Family Services 2015.0 Q1 651 4392.195084 1058.0 2859319.0
51 Children's Family Services 2015.0 Q2 644 4342.894410 891.0 2796824.0
52 Children's Family Services 2015.0 Q3 775 5307.362581 913.0 4113206.0
53 Children's Family Services 2015.0 Q4 692 3635.173410 712.5 2515540.0
54 Children's Family Services 2016.0 Q1 854 4762.837237 1666.5 4067463.0
55 Children's Family Services 2016.0 Q2 778 7270.800771 2130.5 5656683.0
56 Children's Family Services 2016.0 Q3 842 6766.944181 2616.0 5697767.0
57 Children's Family Services 2016.0 Q4 851 8252.323149 3020.0 7022727.0
58 Children's Family Services 2017.0 Q1 763 7900.480996 2500.0 6028067.0
59 Children's Family Services 2017.0 Q2 83 10154.819277 3227.0 842850.0
60 Children's Family Services 2017.0 Q3 90 6971.388889 3120.5 627425.0
61 Children's Family Services 2017.0 Q4 52 8969.653846 2330.0 466422.0
62 Children's Service DSG 2014.0 Q1 13 4744.076923 465.0 61673.0
63 Children's Service DSG 2014.0 Q2 8 7981.250000 2251.0 63850.0
64 Children's Service DSG 2014.0 Q3 24 9905.250000 1523.0 237726.0
65 Children's Service DSG 2014.0 Q4 18 3005.888889 549.0 54106.0
66 Children's Service DSG 2015.0 Q1 16 4445.500000 1825.0 71128.0
67 Children's Service DSG 2015.0 Q2 17 2768.058824 1187.0 47057.0
68 Children's Service DSG 2015.0 Q3 32 5259.343750 2172.5 168299.0
69 Children's Service DSG 2015.0 Q4 41 4032.268293 1121.0 165323.0
70 Children's Service DSG 2016.0 Q1 32 3479.875000 537.0 111356.0
71 Children's Service DSG 2016.0 Q2 23 10609.956522 4300.0 244029.0
72 Children's Service DSG 2016.0 Q3 16 16429.687500 4291.5 262875.0
73 Children's Service DSG 2016.0 Q4 27 9387.407407 4300.0 253460.0
74 Children's Service DSG 2017.0 Q1 6 1539.166667 1034.5 9235.0
75 Children's Service DSG 2017.0 Q3 3 2302.666667 1027.0 6908.0
76 Children's Service DSG 2017.0 Q4 1 690.000000 690.0 690.0
77 Childrens Services 2014.0 Q1 148 9637.871622 865.5 1426405.0
78 Childrens Services 2014.0 Q2 688 4034.363372 625.5 2775642.0
79 Childrens Services 2014.0 Q3 355 4317.707042 554.0 1532786.0
80 Childrens Services 2014.0 Q4 106 4336.358491 1127.0 459654.0
81 Commercial 2014.0 Q2 2 3705.000000 3705.0 7410.0
82 Commercial 2014.0 Q3 7 28625.142857 594.0 200376.0
83 Commissioning 2014.0 Q1 5 394.600000 28.0 1973.0
84 Commissioning 2014.0 Q2 3 1304.333333 345.0 3913.0
85 Commissioning 2014.0 Q3 4 2865.250000 9.5 11461.0
86 Commissioning 2014.0 Q4 32 1685.156250 183.5 53925.0
87 Commissioning 2015.0 Q1 36 27791.583333 573.0 1000497.0
88 Commissioning 2015.0 Q2 13 8074.846154 5825.0 104973.0
89 Commissioning 2015.0 Q3 21 10849.047619 3999.0 227830.0
90 Commissioning 2015.0 Q4 28 5162.857143 837.5 144560.0
91 Commissioning 2016.0 Q1 70 9532.557143 3972.0 667279.0
92 Commissioning 2016.0 Q2 41 20444.707317 7007.0 838233.0
93 Commissioning 2016.0 Q3 62 24879.548387 7632.5 1542532.0
94 Commissioning 2016.0 Q4 47 14434.680851 5825.0 678430.0
95 Commissioning 2017.0 Q1 28 17209.678571 10181.0 481871.0
96 Commissioning 2017.0 Q2 2 30060.000000 30060.0 60120.0
97 Commissioning 2017.0 Q3 2 27228.000000 27228.0 54456.0
98 Commissioning 2017.0 Q4 6 10480.333333 9483.0 62882.0
99 Control Accounts 2014.0 Q1 3 1037.666667 1099.0 3113.0
100 Control Accounts 2014.0 Q2 4 1906.750000 463.5 7627.0
101 Control Accounts 2014.0 Q3 1 8331.000000 8331.0 8331.0
102 Customer Support Group 2014.0 Q1 1 252.000000 252.0 252.0
103 Customer Support Group 2014.0 Q2 5 7593.600000 10952.0 37968.0
104 Customer Support Group 2014.0 Q4 12 4581.750000 2128.5 54981.0
105 Customer Support Group 2015.0 Q1 18 2307.055556 1455.0 41527.0
106 Customer Support Group 2015.0 Q2 11 303437.000000 212400.0 3337807.0
107 Customer Support Group 2015.0 Q3 15 130563.600000 2016.0 1958454.0
108 Customer Support Group 2015.0 Q4 9 123716.777778 1788.0 1113451.0
109 Customer Support Group 2016.0 Q1 7 225514.285714 17100.0 1578600.0
110 Customer Support Group 2016.0 Q2 11 268005.272727 83700.0 2948058.0
111 Customer Support Group 2016.0 Q3 10 193098.000000 57700.0 1930980.0
112 Customer Support Group 2016.0 Q4 8 123429.375000 28650.0 987435.0
113 Customer Support Group 2017.0 Q1 10 221540.000000 110250.0 2215400.0
114 Deputy Chief Operating Officer 2014.0 Q1 10 1892.200000 347.0 18922.0
115 Deputy Chief Operating Officer 2014.0 Q2 37 2153.189189 115.0 79668.0
116 Deputy Chief Operating Officer 2014.0 Q3 38 1342.342105 320.0 51009.0
117 Deputy Chief Operating Officer 2014.0 Q4 28 749.321429 91.0 20981.0
118 Education 2014.0 Q1 17 1343.411765 500.0 22838.0
119 Education 2014.0 Q2 1 372.000000 372.0 372.0
120 Education 2014.0 Q3 46 5964.239130 451.5 274355.0
121 Education 2014.0 Q4 31 5027.709677 1265.0 155859.0
122 Family Services 2014.0 Q1 86 3075.302326 994.0 264476.0
123 Family Services 2014.0 Q2 58 3692.224138 950.5 214149.0
124 Family Services 2014.0 Q3 361 3747.565097 576.0 1352871.0
125 Family Services 2014.0 Q4 265 3751.815094 572.0 994231.0
126 Governance 2014.0 Q1 1 252.000000 252.0 252.0
127 Governance 2014.0 Q2 4 319638.000000 319786.0 1278552.0
128 Governance 2014.0 Q3 1 44493.000000 44493.0 44493.0
129 Governance 2014.0 Q4 2 26698.500000 26698.5 53397.0
130 HRA 2016.0 Q4 1 28994.000000 28994.0 28994.0
131 Internal Audit & CAFT 2014.0 Q1 1 17.000000 17.0 17.0
132 Internal Audit & CAFT 2014.0 Q2 3 3218.000000 4032.0 9654.0
133 Internal Audit & CAFT 2014.0 Q3 5 821.200000 114.0 4106.0
134 Internal Audit & CAFT 2014.0 Q4 2 51.500000 51.5 103.0
135 NSCSO 2014.0 Q2 1 10.000000 10.0 10.0
136 NSCSO 2014.0 Q3 2 877.500000 877.5 1755.0
137 Parking & Infrastructure 2014.0 Q2 1 418.000000 418.0 418.0
138 Parking & Infrastructure 2014.0 Q4 1 5102.000000 5102.0 5102.0
139 Parking & Infrastructure 2015.0 Q1 1 15967.000000 15967.0 15967.0
140 Parking & Infrastructure 2015.0 Q2 1 2843.000000 2843.0 2843.0
141 Parking & Infrastructure 2016.0 Q2 1 50000.000000 50000.0 50000.0
142 Parking & Infrastructure 2016.0 Q3 1 7625.000000 7625.0 7625.0
143 Parking & Infrastructure 2016.0 Q4 2 178412.500000 178412.5 356825.0
144 Parking & Infrastructure 2017.0 Q1 4 5867.000000 5645.5 23468.0
145 Public Health 2014.0 Q1 1 133.000000 133.0 133.0
146 Public Health 2014.0 Q2 1 1095.000000 1095.0 1095.0
147 Public Health 2016.0 Q2 1 455.000000 455.0 455.0
148 Regional Enterprise 2015.0 Q1 2 82280.000000 82280.0 164560.0
149 Regional Enterprise 2016.0 Q1 1 6000.000000 6000.0 6000.0
150 Regional Enterprise 2016.0 Q4 2 11900.000000 11900.0 23800.0
151 Regional Enterprise 2017.0 Q4 1 22600.000000 22600.0 22600.0
152 Strategic Commissioning Board 2014.0 Q2 1 244.000000 244.0 244.0
153 Street Scene 2014.0 Q1 3 4516.666667 1179.0 13550.0
154 Street Scene 2014.0 Q2 9 2280.777778 1176.0 20527.0
155 Street Scene 2014.0 Q3 13 2243.923077 1118.0 29171.0
156 Street Scene 2014.0 Q4 14 2092.285714 470.5 29292.0
157 Streetscene 2014.0 Q1 1 3551.000000 3551.0 3551.0
158 Streetscene 2014.0 Q2 2 26430.000000 26430.0 52860.0
159 Streetscene 2014.0 Q3 1 212.000000 212.0 212.0
160 Streetscene 2014.0 Q4 21 4088.476190 1675.0 85858.0
161 Streetscene 2015.0 Q1 55 5936.254545 2275.0 326494.0
162 Streetscene 2015.0 Q2 25 4518.480000 3394.0 112962.0
163 Streetscene 2015.0 Q3 59 2488.186441 2275.0 146803.0
164 Streetscene 2015.0 Q4 29 11537.103448 1803.0 334576.0
165 Streetscene 2016.0 Q1 25 9902.840000 2925.0 247571.0
166 Streetscene 2016.0 Q2 41 23884.341463 23250.0 979258.0
167 Streetscene 2016.0 Q3 69 16416.333333 20250.0 1132727.0
168 Streetscene 2016.0 Q4 50 9267.980000 7353.5 463399.0
169 Streetscene 2017.0 Q1 38 16501.368421 9929.5 627052.0
170 Streetscene 2017.0 Q2 2 7540.000000 7540.0 15080.0
171 Streetscene 2017.0 Q4 2 8400.000000 8400.0 16800.0

Question 1 part 2¶

summary table -I¶

In [44]:
ad_year_qt
Out[44]:
Account Description year Quarter count mean median sum
0 Advertising 2014.0 Q2 8 4604.000000 5216.0 36832.0
1 Advertising 2014.0 Q3 3 5825.000000 5825.0 17475.0
2 Advertising 2014.0 Q4 5 4659.400000 5825.0 23297.0
3 Advertising 2015.0 Q1 2 5825.000000 5825.0 11650.0
4 Advertising 2015.0 Q2 2 2927.000000 2927.0 5854.0
5 Advertising 2015.0 Q4 2 115110.000000 115110.0 230220.0
6 Advertising 2016.0 Q4 4 47692.500000 60000.0 190770.0
7 Advertising for staff 2016.0 Q2 1 45000.000000 45000.0 45000.0
8 Books-CDs-Audio-Video 2014.0 Q1 25 1547.360000 411.0 38684.0
9 Books-CDs-Audio-Video 2014.0 Q2 50 5371.680000 1513.5 268584.0
10 Books-CDs-Audio-Video 2014.0 Q3 43 4513.279070 1268.0 194071.0
11 Books-CDs-Audio-Video 2014.0 Q4 68 2965.000000 667.5 201620.0
12 Books-CDs-Audio-Video 2015.0 Q1 49 2200.571429 932.0 107828.0
13 Books-CDs-Audio-Video 2015.0 Q2 59 2021.305085 998.0 119257.0
14 Books-CDs-Audio-Video 2015.0 Q3 30 4038.200000 1251.0 121146.0
15 Books-CDs-Audio-Video 2015.0 Q4 34 3656.852941 931.5 124333.0
16 Books-CDs-Audio-Video 2016.0 Q1 22 5356.681818 2333.0 117847.0
17 Books-CDs-Audio-Video 2016.0 Q2 14 13482.571429 3860.5 188756.0
18 Books-CDs-Audio-Video 2016.0 Q3 13 1554.384615 1167.0 20207.0
19 Books-CDs-Audio-Video 2016.0 Q4 9 3742.444444 2000.0 33682.0
20 Books-CDs-Audio-Video 2017.0 Q1 6 9076.333333 6835.0 54458.0
21 Books-CDs-Audio-Video 2017.0 Q3 3 3839.333333 1499.0 11518.0
22 Building Repairs & Maintenance 2014.0 Q1 9 7224.111111 886.0 65017.0
23 Building Repairs & Maintenance 2014.0 Q2 144 1212.055556 335.5 174536.0
24 Building Repairs & Maintenance 2014.0 Q3 40 2960.450000 365.0 118418.0
25 Building Repairs & Maintenance 2014.0 Q4 37 3496.108108 274.0 129356.0
26 Building Repairs & Maintenance 2015.0 Q1 24 14772.000000 500.5 354528.0
27 Building Repairs & Maintenance 2015.0 Q2 16 2374.875000 650.5 37998.0
28 Building Repairs & Maintenance 2015.0 Q3 22 2575.954545 1772.0 56671.0
29 Building Repairs & Maintenance 2015.0 Q4 23 3079.869565 2156.0 70837.0
30 Building Repairs & Maintenance 2016.0 Q1 23 3814.043478 3449.0 87723.0
31 Building Repairs & Maintenance 2016.0 Q2 27 4898.888889 2749.0 132270.0
32 Building Repairs & Maintenance 2016.0 Q3 29 8618.965517 3588.0 249950.0
33 Building Repairs & Maintenance 2016.0 Q4 13 13448.384615 8028.0 174829.0
34 Building Repairs & Maintenance 2017.0 Q1 17 4129.941176 2500.0 70209.0
35 Building Repairs & Maintenance 2017.0 Q2 2 4296.000000 4296.0 8592.0
36 Building Repairs & Maintenance 2017.0 Q3 1 7564.000000 7564.0 7564.0
37 Building Repairs & Maintenance 2017.0 Q4 4 5627.000000 4431.0 22508.0
38 CSG - IT 2016.0 Q4 1 9750.000000 9750.0 9750.0
39 CSG - IT 2017.0 Q1 2 7995.000000 7995.0 15990.0
40 Catering Recharge 2016.0 Q3 1 10000.000000 10000.0 10000.0
41 Cleaning and domestic material 2014.0 Q1 20 1192.850000 271.0 23857.0
42 Cleaning and domestic material 2014.0 Q2 45 2655.355556 1242.0 119491.0
43 Cleaning and domestic material 2014.0 Q3 53 1141.358491 275.0 60492.0
44 Cleaning and domestic material 2014.0 Q4 46 1641.391304 177.5 75504.0
45 Cleaning and domestic material 2015.0 Q1 34 1117.705882 294.0 38002.0
46 Cleaning and domestic material 2015.0 Q2 48 1339.395833 402.5 64291.0
47 Cleaning and domestic material 2015.0 Q3 59 1169.016949 387.0 68972.0
48 Cleaning and domestic material 2015.0 Q4 63 1587.301587 338.0 100000.0
49 Cleaning and domestic material 2016.0 Q1 58 2170.362069 950.0 125881.0
50 Cleaning and domestic material 2016.0 Q2 69 3333.072464 1698.0 229982.0
51 Cleaning and domestic material 2016.0 Q3 71 2850.464789 1925.0 202383.0
52 Cleaning and domestic material 2016.0 Q4 68 3346.250000 1955.0 227545.0
53 Cleaning and domestic material 2017.0 Q1 38 4251.394737 2937.5 161553.0
54 Cleaning and domestic material 2017.0 Q2 7 2556.000000 2565.0 17892.0
55 Cleaning and domestic material 2017.0 Q3 6 2671.500000 3158.0 16029.0
56 Cleaning and domestic material 2017.0 Q4 3 2685.666667 2660.0 8057.0
57 Clothing - Protective Clothing 2014.0 Q2 1 9714.000000 9714.0 9714.0
58 Clothing - Protective Clothing 2014.0 Q3 1 116.000000 116.0 116.0
59 Clothing - Protective Clothing 2015.0 Q1 2 3598.000000 3598.0 7196.0
60 Clothing - Protective Clothing 2015.0 Q3 1 124.000000 124.0 124.0
61 Clothing - Protective Clothing 2015.0 Q4 1 1989.000000 1989.0 1989.0
62 Clothing - Protective Clothing 2016.0 Q1 1 1833.000000 1833.0 1833.0
63 Clothing - Protective Clothing 2016.0 Q2 1 4800.000000 4800.0 4800.0
64 Clothing - Protective Clothing 2016.0 Q3 1 6500.000000 6500.0 6500.0
65 Clothing - Protective Clothing 2016.0 Q4 2 4247.500000 4247.5 8495.0
66 Clothing - Protective Clothing 2017.0 Q1 3 5122.000000 6000.0 15366.0
67 Clothing - Uniforms 2014.0 Q3 2 321.500000 321.5 643.0
68 Conference Expenses 2014.0 Q2 1 2988.000000 2988.0 2988.0
69 Conference Expenses 2014.0 Q4 1 420.000000 420.0 420.0
70 Conference Expenses 2015.0 Q1 3 7816.666667 180.0 23450.0
71 Conference Expenses 2015.0 Q2 2 1093.000000 1093.0 2186.0
72 Conference Expenses 2015.0 Q3 4 302.500000 230.0 1210.0
73 Conference Expenses 2015.0 Q4 1 1788.000000 1788.0 1788.0
74 Conference Expenses 2016.0 Q1 5 23150.600000 378.0 115753.0
75 Conference Expenses 2016.0 Q2 4 30445.000000 33840.0 121780.0
76 Conference Expenses 2016.0 Q3 4 51567.750000 61250.0 206271.0
77 Conference Expenses 2016.0 Q4 5 18014.800000 21000.0 90074.0
78 Conference Expenses 2017.0 Q1 1 119400.000000 119400.0 119400.0
79 Consultants Fees 2016.0 Q1 1 6000.000000 6000.0 6000.0
80 Consultants Fees 2016.0 Q2 1 90000.000000 90000.0 90000.0
81 Consumable Catering Supplies 2014.0 Q1 3 1902.000000 1638.0 5706.0
82 Consumable Catering Supplies 2014.0 Q2 5 20814.400000 25853.0 104072.0
83 Consumable Catering Supplies 2014.0 Q3 8 11281.500000 1665.5 90252.0
84 Consumable Catering Supplies 2014.0 Q4 12 5011.583333 2362.0 60139.0
85 Consumable Catering Supplies 2015.0 Q1 3 18436.666667 27045.0 55310.0
86 Consumable Catering Supplies 2015.0 Q2 6 3101.166667 1839.5 18607.0
87 Consumable Catering Supplies 2015.0 Q3 2 1638.000000 1638.0 3276.0
88 Consumable Catering Supplies 2015.0 Q4 4 5608.000000 390.0 22432.0
89 Consumable Catering Supplies 2016.0 Q1 1 14333.000000 14333.0 14333.0
90 Consumable Catering Supplies 2016.0 Q2 2 1300.500000 1300.5 2601.0
91 Consumable Catering Supplies 2016.0 Q3 1 998.000000 998.0 998.0
92 Consumable Catering Supplies 2017.0 Q1 5 2925.200000 1496.0 14626.0
93 Counsels Fees 2016.0 Q1 1 120000.000000 120000.0 120000.0
94 E19 - Learning Resources 2014.0 Q4 5 140.600000 98.0 703.0
95 E19 - Learning Resources 2015.0 Q1 10 2207.600000 949.5 22076.0
96 E19 - Learning Resources 2015.0 Q2 4 6347.000000 607.5 25388.0
97 E19 - Learning Resources 2015.0 Q3 10 2482.400000 2383.0 24824.0
98 E19 - Learning Resources 2015.0 Q4 9 3450.222222 1083.0 31052.0
99 E19 - Learning Resources 2016.0 Q1 7 2684.857143 2413.0 18794.0
100 E19 - Learning Resources 2016.0 Q4 1 44.000000 44.0 44.0
101 E25 - Catering Supplies 2015.0 Q1 3 6833.333333 2705.0 20500.0
102 E25 - Catering Supplies 2015.0 Q2 6 5848.500000 4475.0 35091.0
103 E25 - Catering Supplies 2015.0 Q3 10 9059.100000 11552.5 90591.0
104 E25 - Catering Supplies 2015.0 Q4 13 7743.923077 11494.0 100671.0
105 E25 - Catering Supplies 2016.0 Q1 7 8729.285714 9179.0 61105.0
106 E25 - Catering Supplies 2016.0 Q2 1 9725.000000 9725.0 9725.0
107 E25 - Catering Supplies 2016.0 Q3 4 4350.000000 2265.5 17400.0
108 E25 - Catering Supplies 2016.0 Q4 2 5523.000000 5523.0 11046.0
109 Education CFR Administrative S 2015.0 Q1 5 6869.200000 2518.0 34346.0
110 Education CFR Administrative S 2015.0 Q2 2 2380.000000 2380.0 4760.0
111 Education CFR Administrative S 2015.0 Q3 10 7632.000000 3412.0 76320.0
112 Education CFR Administrative S 2015.0 Q4 13 7190.230769 3333.0 93473.0
113 Education CFR Administrative S 2016.0 Q1 14 7234.142857 1467.0 101278.0
114 Education CFR Administrative S 2016.0 Q3 1 21665.000000 21665.0 21665.0
115 Education CFR Administrative S 2016.0 Q4 1 126.000000 126.0 126.0
116 Education CFR Other Occupation 2015.0 Q1 1 2333.000000 2333.0 2333.0
117 Education CFR Other Occupation 2015.0 Q2 1 663.000000 663.0 663.0
118 Education CFR Other Occupation 2015.0 Q4 1 1582.000000 1582.0 1582.0
119 Education CFR Other Occupation 2016.0 Q1 2 596.500000 596.5 1193.0
120 Electricity 2015.0 Q3 1 14699.000000 14699.0 14699.0
121 Electricity 2016.0 Q2 4 33475.250000 34202.0 133901.0
122 Electricity 2017.0 Q2 1 52703.000000 52703.0 52703.0
123 Electricity 2017.0 Q4 1 356903.000000 356903.0 356903.0
124 Employer's National Insurance 2015.0 Q4 1 667.000000 667.0 667.0
125 Equipment Hire 2016.0 Q2 2 74460.000000 74460.0 148920.0
126 Equipment Hire 2016.0 Q3 2 13700.000000 13700.0 27400.0
127 Equipment Hire 2016.0 Q4 1 10000.000000 10000.0 10000.0
128 Equipment and Materials Purcha 2014.0 Q1 101 4835.712871 1298.0 488407.0
129 Equipment and Materials Purcha 2014.0 Q2 212 3796.198113 1137.0 804794.0
130 Equipment and Materials Purcha 2014.0 Q3 221 5645.520362 934.0 1247660.0
131 Equipment and Materials Purcha 2014.0 Q4 256 4099.832031 976.5 1049557.0
132 Equipment and Materials Purcha 2015.0 Q1 189 5027.645503 2153.0 950225.0
133 Equipment and Materials Purcha 2015.0 Q2 174 5046.287356 1899.0 878054.0
134 Equipment and Materials Purcha 2015.0 Q3 258 6685.197674 1375.5 1724781.0
135 Equipment and Materials Purcha 2015.0 Q4 194 3957.561856 1308.0 767767.0
136 Equipment and Materials Purcha 2016.0 Q1 225 5787.524444 2591.0 1302193.0
137 Equipment and Materials Purcha 2016.0 Q2 148 8085.087838 3410.0 1196593.0
138 Equipment and Materials Purcha 2016.0 Q3 271 9258.583026 3791.0 2509076.0
139 Equipment and Materials Purcha 2016.0 Q4 213 8903.140845 3318.0 1896369.0
140 Equipment and Materials Purcha 2017.0 Q1 162 8999.518519 3757.0 1457922.0
141 Equipment and Materials Purcha 2017.0 Q2 19 16787.631579 1995.0 318965.0
142 Equipment and Materials Purcha 2017.0 Q3 20 4432.650000 1627.0 88653.0
143 Equipment and Materials Purcha 2017.0 Q4 13 11516.692308 5700.0 149717.0
144 Equipment and Materials Repair 2014.0 Q1 2 26407.000000 26407.0 52814.0
145 Equipment and Materials Repair 2014.0 Q2 1 4816.000000 4816.0 4816.0
146 Equipment and Materials Repair 2014.0 Q3 2 228.000000 228.0 456.0
147 Equipment and Materials Repair 2014.0 Q4 5 15656.800000 2829.0 78284.0
148 Equipment and Materials Repair 2015.0 Q1 4 16917.250000 7395.0 67669.0
149 Equipment and Materials Repair 2015.0 Q2 4 4854.750000 940.5 19419.0
150 Equipment and Materials Repair 2015.0 Q3 6 16603.500000 17157.5 99621.0
151 Equipment and Materials Repair 2015.0 Q4 4 1800.750000 1677.5 7203.0
152 Equipment and Materials Repair 2016.0 Q1 4 3898.750000 2241.0 15595.0
153 Equipment and Materials Repair 2016.0 Q2 3 5082.666667 4848.0 15248.0
154 Equipment and Materials Repair 2017.0 Q1 1 21388.000000 21388.0 21388.0
155 Fees and Charges 2015.0 Q1 3 1058.666667 288.0 3176.0
156 Fees and Charges 2015.0 Q2 5 402535.200000 153600.0 2012676.0
157 Fees and Charges 2015.0 Q3 7 267993.714286 156800.0 1875956.0
158 Fees and Charges 2015.0 Q4 3 369586.666667 410160.0 1108760.0
159 Fees and Charges 2016.0 Q1 7 225187.571429 17100.0 1576313.0
160 Fees and Charges 2016.0 Q2 2 339.500000 339.5 679.0
161 Fees and Charges 2017.0 Q1 1 98000.000000 98000.0 98000.0
162 Fixtures and fittings 2016.0 Q3 3 47672.000000 11400.0 143016.0
163 Fixtures and fittings 2017.0 Q1 1 6150.000000 6150.0 6150.0
164 Food Costs 2014.0 Q1 54 5940.037037 2154.0 320762.0
165 Food Costs 2014.0 Q2 172 6346.377907 2392.0 1091577.0
166 Food Costs 2014.0 Q3 196 7147.341837 3084.0 1400879.0
167 Food Costs 2014.0 Q4 175 6784.045714 2204.0 1187208.0
168 Food Costs 2015.0 Q1 163 6451.325153 2059.0 1051566.0
169 Food Costs 2015.0 Q2 170 7926.594118 2438.0 1347521.0
170 Food Costs 2015.0 Q3 183 7547.005464 2226.0 1381102.0
171 Food Costs 2015.0 Q4 172 7691.081395 2785.5 1322866.0
172 Food Costs 2016.0 Q1 197 5493.142132 1438.0 1082149.0
173 Food Costs 2016.0 Q2 201 5934.427861 2000.0 1192820.0
174 Food Costs 2016.0 Q3 196 5760.454082 2793.0 1129049.0
175 Food Costs 2016.0 Q4 180 6222.716667 3334.0 1120089.0
176 Food Costs 2017.0 Q1 134 6466.029851 3527.0 866448.0
177 Food Costs 2017.0 Q2 15 5862.866667 4715.0 87943.0
178 Food Costs 2017.0 Q3 19 6590.368421 3512.0 125217.0
179 Food Costs 2017.0 Q4 18 3960.111111 1039.5 71282.0
180 Furniture-Purchase-Repair 2015.0 Q1 1 398400.000000 398400.0 398400.0
181 Furniture-Purchase-Repair 2016.0 Q2 1 11166.000000 11166.0 11166.0
182 Furniture-Purchase-Repair 2016.0 Q3 1 429.000000 429.0 429.0
183 Gas 2016.0 Q2 2 94471.000000 94471.0 188942.0
184 Gas 2016.0 Q4 1 20370.000000 20370.0 20370.0
185 Gas 2017.0 Q1 1 85400.000000 85400.0 85400.0
186 General Office Expenses 2014.0 Q2 1 120.000000 120.0 120.0
187 General Office Expenses 2014.0 Q4 1 10.000000 10.0 10.0
188 General Office Expenses 2015.0 Q1 3 1015.000000 1029.0 3045.0
189 General Office Expenses 2015.0 Q2 7 20705.000000 1455.0 144935.0
190 General Office Expenses 2015.0 Q3 8 8859.875000 3482.5 70879.0
191 General Office Expenses 2015.0 Q4 5 4308.000000 4321.0 21540.0
192 General Office Expenses 2016.0 Q1 7 2883.142857 2998.0 20182.0
193 General Office Expenses 2016.0 Q2 16 5390.750000 4424.5 86252.0
194 General Office Expenses 2016.0 Q3 14 9503.428571 5737.5 133048.0
195 General Office Expenses 2016.0 Q4 26 5717.230769 3197.0 148648.0
196 General Office Expenses 2017.0 Q1 22 8358.863636 4081.5 183895.0
197 General Office Expenses 2017.0 Q3 1 333.000000 333.0 333.0
198 General Office Expenses 2017.0 Q4 1 5625.000000 5625.0 5625.0
199 Grant Payments 2015.0 Q3 2 330.000000 330.0 660.0
200 Grounds maintenance 2014.0 Q1 1 340.000000 340.0 340.0
201 Grounds maintenance 2014.0 Q2 2 2635.500000 2635.5 5271.0
202 Grounds maintenance 2014.0 Q3 2 7074.500000 7074.5 14149.0
203 Grounds maintenance 2014.0 Q4 3 5540.333333 500.0 16621.0
204 Grounds maintenance 2015.0 Q2 1 340.000000 340.0 340.0
205 Grounds maintenance 2016.0 Q2 2 13625.000000 13625.0 27250.0
206 Grounds maintenance 2017.0 Q1 2 3897.000000 3897.0 7794.0
207 Grounds maintenance 2017.0 Q4 1 1998.000000 1998.0 1998.0
208 Hardware Purchases 2014.0 Q4 2 2849.000000 2849.0 5698.0
209 Hardware Purchases 2015.0 Q4 2 2900.000000 2900.0 5800.0
210 Hardware Purchases 2016.0 Q1 1 977.000000 977.0 977.0
211 Hardware Purchases 2016.0 Q2 2 5977.500000 5977.5 11955.0
212 Hardware Purchases 2016.0 Q4 1 13601.000000 13601.0 13601.0
213 Hardware Purchases 2017.0 Q1 2 4954.500000 4954.5 9909.0
214 Hardware Purchases 2017.0 Q2 1 12687.000000 12687.0 12687.0
215 Hardware Purchases 2017.0 Q3 1 3279.000000 3279.0 3279.0
216 IT Services 2014.0 Q1 1 11433.000000 11433.0 11433.0
217 IT Services 2014.0 Q2 4 9386.500000 11433.0 37546.0
218 IT Services 2014.0 Q3 4 3308.000000 851.0 13232.0
219 IT Services 2014.0 Q4 6 3430.666667 782.5 20584.0
220 IT Services 2015.0 Q1 1 6605.000000 6605.0 6605.0
221 IT Services 2015.0 Q2 1 11433.000000 11433.0 11433.0
222 IT Services 2015.0 Q3 3 7642.000000 11433.0 22926.0
223 Laundry and Dry Cleaning 2016.0 Q1 1 1545.000000 1545.0 1545.0
224 Laundry and Dry Cleaning 2016.0 Q2 2 1150.000000 1150.0 2300.0
225 Laundry and Dry Cleaning 2016.0 Q3 2 1600.000000 1600.0 3200.0
226 Laundry and Dry Cleaning 2016.0 Q4 1 1150.000000 1150.0 1150.0
227 Laundry and Dry Cleaning 2017.0 Q1 2 1250.000000 1250.0 2500.0
228 Laundry and Dry Cleaning 2017.0 Q3 1 1150.000000 1150.0 1150.0
229 Legal and Court Fees 2014.0 Q1 11 460.363636 336.0 5064.0
230 Legal and Court Fees 2014.0 Q2 23 133860.608696 925.0 3078794.0
231 Legal and Court Fees 2014.0 Q3 20 60536.550000 692.0 1210731.0
232 Legal and Court Fees 2014.0 Q4 14 2032.142857 273.0 28450.0
233 Legal and Court Fees 2015.0 Q1 22 1722.136364 2081.5 37887.0
234 Legal and Court Fees 2015.0 Q2 6 220640.500000 228700.0 1323843.0
235 Legal and Court Fees 2015.0 Q3 5 13549.400000 500.0 67747.0
236 Legal and Court Fees 2015.0 Q4 1 500.000000 500.0 500.0
237 Legal and Court Fees 2016.0 Q1 1 40.000000 40.0 40.0
238 Legal and Court Fees 2016.0 Q2 8 355474.875000 110250.0 2843799.0
239 Legal and Court Fees 2016.0 Q3 6 314633.333333 316100.0 1887800.0
240 Legal and Court Fees 2016.0 Q4 7 144964.285714 73500.0 1014750.0
241 Legal and Court Fees 2017.0 Q1 7 293914.285714 171500.0 2057400.0
242 Miscellaneous Expenses 2014.0 Q1 14 18231.214286 585.0 255237.0
243 Miscellaneous Expenses 2014.0 Q2 29 1256.689655 234.0 36444.0
244 Miscellaneous Expenses 2014.0 Q3 22 6088.500000 234.0 133947.0
245 Miscellaneous Expenses 2014.0 Q4 20 4742.700000 234.0 94854.0
246 Miscellaneous Expenses 2015.0 Q1 27 2993.629630 279.0 80828.0
247 Miscellaneous Expenses 2015.0 Q2 53 4012.547170 333.0 212665.0
248 Miscellaneous Expenses 2015.0 Q3 54 858.314815 164.5 46349.0
249 Miscellaneous Expenses 2015.0 Q4 63 3758.761905 762.0 236802.0
250 Miscellaneous Expenses 2016.0 Q1 69 7327.144928 2247.0 505573.0
251 Miscellaneous Expenses 2016.0 Q2 59 14005.016949 3894.0 826296.0
252 Miscellaneous Expenses 2016.0 Q3 56 9071.803571 3979.5 508021.0
253 Miscellaneous Expenses 2016.0 Q4 62 23363.532258 8762.5 1448539.0
254 Miscellaneous Expenses 2017.0 Q1 58 18551.534483 4602.0 1075989.0
255 Miscellaneous Expenses 2017.0 Q2 2 7381.500000 7381.5 14763.0
256 Miscellaneous Expenses 2017.0 Q3 9 17170.888889 8692.0 154538.0
257 Miscellaneous Expenses 2017.0 Q4 6 31024.833333 12183.0 186149.0
258 NNDR Collected 2016.0 Q2 2 205.000000 205.0 410.0
259 Non Education Staff GPay 2016.0 Q2 1 6080.000000 6080.0 6080.0
260 Non Education Staff GPay 2016.0 Q4 2 7500.000000 7500.0 15000.0
261 Operating Leases - Transport 2016.0 Q1 1 6583.000000 6583.0 6583.0
262 Operating Leases - Transport 2017.0 Q1 7 10214.285714 6500.0 71500.0
263 Other Agencies - Third Party P 2015.0 Q1 7 42971.714286 8704.0 300802.0
264 Other Agencies - Third Party P 2015.0 Q2 11 12545.181818 2325.0 137997.0
265 Other Agencies - Third Party P 2015.0 Q3 27 11962.703704 1394.0 322993.0
266 Other Agencies - Third Party P 2015.0 Q4 7 1766.571429 171.0 12366.0
267 Other Agencies - Third Party P 2016.0 Q1 3 114.333333 79.0 343.0
268 Other Agencies - Third Party P 2016.0 Q3 3 52278.000000 55136.0 156834.0
269 Other Agencies - Third Party P 2016.0 Q4 4 107955.750000 122750.0 431823.0
270 Other Agencies - Third Party P 2017.0 Q1 4 58231.250000 51287.5 232925.0
271 Other Agencies - Third Party P 2017.0 Q2 1 19900.000000 19900.0 19900.0
272 Other Agencies - Third Party P 2017.0 Q3 1 7925.000000 7925.0 7925.0
273 Other Energy 2014.0 Q4 6 13323.666667 12263.5 79942.0
274 Other Energy 2015.0 Q1 1 26993.000000 26993.0 26993.0
275 Other Establishments - Third P 2015.0 Q3 2 5043.000000 5043.0 10086.0
276 Other Establishments - Third P 2016.0 Q1 1 7644.000000 7644.0 7644.0
277 Other Establishments - Third P 2016.0 Q3 1 20031.000000 20031.0 20031.0
278 Other Indirect Employee Expens 2014.0 Q2 1 40.000000 40.0 40.0
279 Other Indirect Employee Expens 2014.0 Q3 6 2046.000000 1852.0 12276.0
280 Other Indirect Employee Expens 2015.0 Q1 2 4445.500000 4445.5 8891.0
281 Other Indirect Employee Expens 2015.0 Q2 5 4431.000000 1323.0 22155.0
282 Other Indirect Employee Expens 2015.0 Q3 5 4056.200000 2628.0 20281.0
283 Other Indirect Employee Expens 2015.0 Q4 4 3336.750000 3696.0 13347.0
284 Other Indirect Employee Expens 2016.0 Q1 5 2260.200000 1667.0 11301.0
285 Other Indirect Employee Expens 2016.0 Q2 4 1242.750000 1433.0 4971.0
286 Other Indirect Employee Expens 2016.0 Q3 5 3071.200000 1952.0 15356.0
287 Other Indirect Employee Expens 2016.0 Q4 9 994.555556 556.0 8951.0
288 Other Indirect Employee Expens 2017.0 Q1 7 3254.428571 3708.0 22781.0
289 Other Indirect Employee Expens 2017.0 Q3 4 576.500000 589.5 2306.0
290 Other Services 2014.0 Q1 6 102652.333333 2521.5 615914.0
291 Other Services 2014.0 Q2 27 49287.888889 273.0 1330773.0
292 Other Services 2014.0 Q3 40 1391.075000 206.0 55643.0
293 Other Services 2014.0 Q4 27 603.925926 171.0 16306.0
294 Other Services 2015.0 Q1 51 3972.549020 406.0 202600.0
295 Other Services 2015.0 Q2 58 3010.913793 1123.0 174633.0
296 Other Services 2015.0 Q3 64 6476.031250 764.0 414466.0
297 Other Services 2015.0 Q4 84 3625.202381 617.0 304517.0
298 Other Services 2016.0 Q1 141 3933.453901 1615.0 554617.0
299 Other Services 2016.0 Q2 102 6644.039216 1974.0 677692.0
300 Other Services 2016.0 Q3 84 5443.678571 3080.5 457269.0
301 Other Services 2016.0 Q4 117 7168.803419 3394.0 838750.0
302 Other Services 2017.0 Q1 150 8548.400000 2499.5 1282260.0
303 Other Services 2017.0 Q2 9 13262.777778 3227.0 119365.0
304 Other Services 2017.0 Q3 7 3908.571429 3307.0 27360.0
305 Other Services 2017.0 Q4 4 12648.000000 12918.0 50592.0
306 Other Transfer Payments to Soc 2014.0 Q1 18 442.944444 92.5 7973.0
307 Other Transfer Payments to Soc 2014.0 Q2 33 824.606061 265.0 27212.0
308 Other Transfer Payments to Soc 2014.0 Q3 55 1648.272727 127.0 90655.0
309 Other Transfer Payments to Soc 2014.0 Q4 69 4467.014493 500.0 308224.0
310 Other Transfer Payments to Soc 2015.0 Q1 35 4481.200000 506.0 156842.0
311 Other Transfer Payments to Soc 2015.0 Q2 36 4918.305556 160.5 177059.0
312 Other Transfer Payments to Soc 2015.0 Q3 63 5791.317460 809.0 364853.0
313 Other Transfer Payments to Soc 2015.0 Q4 56 2704.196429 402.5 151435.0
314 Other Transfer Payments to Soc 2016.0 Q1 73 6494.671233 2184.0 474111.0
315 Other Transfer Payments to Soc 2016.0 Q2 93 10489.096774 3400.0 975486.0
316 Other Transfer Payments to Soc 2016.0 Q3 76 9786.263158 2999.0 743756.0
317 Other Transfer Payments to Soc 2016.0 Q4 122 11837.631148 5608.0 1444191.0
318 Other Transfer Payments to Soc 2017.0 Q1 95 8084.968421 2538.0 768072.0
319 Other Transfer Payments to Soc 2017.0 Q2 22 9444.000000 4851.5 207768.0
320 Other Transfer Payments to Soc 2017.0 Q3 15 6100.066667 3499.0 91501.0
321 Other Transfer Payments to Soc 2017.0 Q4 4 11326.500000 1057.5 45306.0
322 Other Vehicle Costs 2014.0 Q2 1 53445.000000 53445.0 53445.0
323 Other Vehicle Costs 2015.0 Q2 3 115.000000 115.0 345.0
324 Other Vehicle Costs 2015.0 Q3 5 90.800000 115.0 454.0
325 Other Vehicle Costs 2015.0 Q4 3 115.000000 115.0 345.0
326 Other Vehicle Costs 2016.0 Q1 3 742.666667 115.0 2228.0
327 Other Vehicle Costs 2016.0 Q2 5 13516.200000 4166.0 67581.0
328 Other Vehicle Costs 2016.0 Q3 4 3723.500000 3292.0 14894.0
329 Other Vehicle Costs 2016.0 Q4 3 733.333333 750.0 2200.0
330 Parking Permit Fees 2016.0 Q1 1 930.000000 930.0 930.0
331 Parking Permit Fees 2016.0 Q2 3 270.000000 170.0 810.0
332 Parking Permit Fees 2016.0 Q3 5 577.200000 575.0 2886.0
333 Parking Permit Fees 2016.0 Q4 2 205.000000 205.0 410.0
334 Pool Transport Charges 2015.0 Q1 2 289.000000 289.0 578.0
335 Pool Transport Charges 2015.0 Q2 1 7834.000000 7834.0 7834.0
336 Pool Transport Charges 2015.0 Q3 7 218.571429 289.0 1530.0
337 Pool Transport Charges 2015.0 Q4 9 289.000000 289.0 2601.0
338 Postage 2014.0 Q1 5 409.400000 398.0 2047.0
339 Postage 2014.0 Q2 11 2881.727273 372.0 31699.0
340 Postage 2014.0 Q3 17 458.588235 124.0 7796.0
341 Postage 2014.0 Q4 15 828.466667 124.0 12427.0
342 Postage 2015.0 Q1 15 298.600000 124.0 4479.0
343 Postage 2015.0 Q2 13 643.000000 189.0 8359.0
344 Postage 2015.0 Q3 12 740.000000 189.0 8880.0
345 Postage 2015.0 Q4 7 752.428571 189.0 5267.0
346 Postage 2016.0 Q1 7 2886.714286 450.0 20207.0
347 Postage 2016.0 Q2 13 7083.230769 5500.0 92082.0
348 Postage 2016.0 Q3 13 5971.846154 5500.0 77634.0
349 Postage 2016.0 Q4 8 6722.750000 5500.0 53782.0
350 Postage 2017.0 Q1 3 9450.000000 12800.0 28350.0
351 Printing-Contract 2015.0 Q3 1 21635.000000 21635.0 21635.0
352 Printing-Contract 2015.0 Q4 1 4893.000000 4893.0 4893.0
353 Printing-Contract 2016.0 Q1 3 524.000000 312.0 1572.0
354 Printing-Contract 2016.0 Q4 1 415.000000 415.0 415.0
355 Private Contractors - Third Pa 2014.0 Q1 20 2934.450000 1052.0 58689.0
356 Private Contractors - Third Pa 2014.0 Q2 9 3891.333333 1675.0 35022.0
357 Private Contractors - Third Pa 2014.0 Q3 27 7751.962963 118.0 209303.0
358 Private Contractors - Third Pa 2014.0 Q4 75 1293.333333 572.0 97000.0
359 Private Contractors - Third Pa 2015.0 Q1 6 5172.166667 117.5 31033.0
360 Private Contractors - Third Pa 2015.0 Q2 6 5652.666667 5904.0 33916.0
361 Private Contractors - Third Pa 2015.0 Q3 1 1000.000000 1000.0 1000.0
362 Private Contractors - Third Pa 2015.0 Q4 1 2034.000000 2034.0 2034.0
363 Professional Services 2014.0 Q1 2 49578.500000 49578.5 99157.0
364 Professional Services 2014.0 Q2 2 4597.000000 4597.0 9194.0
365 Professional Services 2014.0 Q3 3 569.666667 173.0 1709.0
366 Professional Services 2014.0 Q4 4 3866.750000 92.5 15467.0
367 Professional Services 2015.0 Q1 5 89482.800000 132662.0 447414.0
368 Professional Services 2015.0 Q3 1 174.000000 174.0 174.0
369 Professional Services 2015.0 Q4 2 3729.000000 3729.0 7458.0
370 Professional Services 2016.0 Q1 7 3961.714286 720.0 27732.0
371 Professional Services 2016.0 Q2 4 49692.500000 59800.0 198770.0
372 Professional Services 2016.0 Q3 5 12665.600000 14500.0 63328.0
373 Professional Services 2017.0 Q1 1 32500.000000 32500.0 32500.0
374 Professional Services 2017.0 Q3 2 36720.000000 36720.0 73440.0
375 Publications 2014.0 Q1 2 55.000000 55.0 110.0
376 Publications 2014.0 Q2 3 4655.000000 55.0 13965.0
377 Publications 2014.0 Q3 12 217.000000 55.0 2604.0
378 Publications 2014.0 Q4 16 319.687500 55.0 5115.0
379 Publications 2015.0 Q1 14 251.142857 55.0 3516.0
380 Publications 2015.0 Q2 12 13621.583333 55.0 163459.0
381 Publications 2015.0 Q3 19 216.105263 6.0 4106.0
382 Publications 2015.0 Q4 13 220.230769 6.0 2863.0
383 Publications 2016.0 Q1 10 493.600000 34.0 4936.0
384 Publications 2016.0 Q2 7 3320.714286 65.0 23245.0
385 Publications 2016.0 Q3 11 1778.363636 65.0 19562.0
386 Publications 2016.0 Q4 10 775.200000 65.0 7752.0
387 Publications 2017.0 Q1 32 2568.656250 1182.0 82197.0
388 Publications 2017.0 Q2 1 65.000000 65.0 65.0
389 Publications 2017.0 Q3 2 4922.500000 4922.5 9845.0
390 Publications 2017.0 Q4 2 1388.500000 1388.5 2777.0
391 Rents 2016.0 Q2 4 87559.750000 21621.0 350239.0
392 Rents 2016.0 Q3 3 23900.000000 23900.0 71700.0
393 Rents 2016.0 Q4 1 4165.000000 4165.0 4165.0
394 Software Licences & Support 2014.0 Q2 2 945.000000 945.0 1890.0
395 Software Licences & Support 2015.0 Q3 1 719.000000 719.0 719.0
396 Software Licences & Support 2015.0 Q4 2 5599.500000 5599.5 11199.0
397 Software Licences & Support 2016.0 Q3 1 798.000000 798.0 798.0
398 Software Licences & Support 2016.0 Q4 1 1398.000000 1398.0 1398.0
399 Software Purchases 2017.0 Q1 1 28841.000000 28841.0 28841.0
400 Stationery 2014.0 Q1 3 2058.000000 569.0 6174.0
401 Stationery 2014.0 Q2 20 3486.850000 1708.0 69737.0
402 Stationery 2014.0 Q3 25 8431.240000 1647.0 210781.0
403 Stationery 2014.0 Q4 18 4509.833333 368.5 81177.0
404 Stationery 2015.0 Q1 17 2896.470588 1699.0 49240.0
405 Stationery 2015.0 Q2 19 2869.421053 1406.0 54519.0
406 Stationery 2015.0 Q3 19 4044.526316 1499.0 76846.0
407 Stationery 2015.0 Q4 26 2951.807692 1027.5 76747.0
408 Stationery 2016.0 Q1 24 2093.333333 833.5 50240.0
409 Stationery 2016.0 Q2 41 4089.414634 897.0 167666.0
410 Stationery 2016.0 Q3 31 2333.096774 1100.0 72326.0
411 Stationery 2016.0 Q4 33 2951.606061 1379.0 97403.0
412 Stationery 2017.0 Q1 24 3062.375000 828.0 73497.0
413 Stationery 2017.0 Q2 3 1745.000000 1700.0 5235.0
414 Stationery 2017.0 Q3 7 2775.857143 1700.0 19431.0
415 Stationery 2017.0 Q4 2 2391.500000 2391.5 4783.0
416 Subscriptions 2014.0 Q1 4 8559.250000 1212.5 34237.0
417 Subscriptions 2014.0 Q2 9 3382.111111 300.0 30439.0
418 Subscriptions 2014.0 Q3 9 983.000000 200.0 8847.0
419 Subscriptions 2014.0 Q4 9 3562.888889 342.0 32066.0
420 Subscriptions 2015.0 Q1 6 3817.833333 2595.0 22907.0
421 Subscriptions 2015.0 Q2 5 1297.200000 210.0 6486.0
422 Subscriptions 2015.0 Q3 8 3241.125000 1288.5 25929.0
423 Subscriptions 2015.0 Q4 9 120.555556 108.0 1085.0
424 Subscriptions 2016.0 Q1 14 3830.214286 617.5 53623.0
425 Subscriptions 2016.0 Q2 8 4741.625000 1724.5 37933.0
426 Subscriptions 2016.0 Q3 10 20744.400000 4350.0 207444.0
427 Subscriptions 2016.0 Q4 14 6210.214286 6450.0 86943.0
428 Subscriptions 2017.0 Q1 6 11249.666667 2049.0 67498.0
429 Subscriptions 2017.0 Q2 1 12000.000000 12000.0 12000.0
430 Subscriptions 2017.0 Q3 1 7900.000000 7900.0 7900.0
431 Subscriptions 2017.0 Q4 2 7800.000000 7800.0 15600.0
432 Subsistence 2014.0 Q1 7 1125.285714 435.0 7877.0
433 Subsistence 2014.0 Q2 19 1388.157895 236.0 26375.0
434 Subsistence 2014.0 Q3 11 720.181818 497.0 7922.0
435 Subsistence 2014.0 Q4 23 292.913043 172.0 6737.0
436 Subsistence 2015.0 Q1 10 1203.100000 305.0 12031.0
437 Subsistence 2015.0 Q2 5 744.200000 337.0 3721.0
438 Subsistence 2016.0 Q1 1 561.000000 561.0 561.0
439 Subsistence 2016.0 Q2 10 367.500000 268.5 3675.0
440 Subsistence 2016.0 Q3 6 652.000000 448.5 3912.0
441 Subsistence 2016.0 Q4 10 811.400000 367.0 8114.0
442 Subsistence 2017.0 Q1 6 345.833333 291.0 2075.0
443 Telephone Rentals 2014.0 Q3 1 72.000000 72.0 72.0
444 Telephone Rentals 2016.0 Q1 2 8870.000000 8870.0 17740.0
445 Telephone Rentals 2016.0 Q2 5 11871.400000 8334.0 59357.0
446 Telephone Rentals 2016.0 Q3 3 8584.666667 8459.0 25754.0
447 Telephone Rentals 2016.0 Q4 4 10437.250000 9780.5 41749.0
448 Telephone Rentals 2017.0 Q1 3 10042.666667 10239.0 30128.0
449 Telephones Calls 2014.0 Q1 2 14393.000000 14393.0 28786.0
450 Telephones Calls 2014.0 Q2 8 12175.000000 9639.5 97400.0
451 Telephones Calls 2014.0 Q3 11 8660.727273 20.0 95268.0
452 Telephones Calls 2014.0 Q4 10 4014.000000 15.0 40140.0
453 Telephones Calls 2015.0 Q1 11 12277.363636 8388.0 135051.0
454 Telephones Calls 2015.0 Q2 10 8690.500000 923.0 86905.0
455 Telephones Calls 2015.0 Q3 6 7941.500000 6742.5 47649.0
456 Telephones Calls 2015.0 Q4 6 3443.833333 2801.0 20663.0
457 Telephones Calls 2016.0 Q1 5 9582.200000 6990.0 47911.0
458 Telephones Calls 2016.0 Q2 11 6074.000000 5589.0 66814.0
459 Telephones Calls 2016.0 Q3 7 4294.142857 5308.0 30059.0
460 Telephones Calls 2016.0 Q4 6 10708.833333 11178.0 64253.0
461 Telephones Calls 2017.0 Q1 3 17429.666667 21160.0 52289.0
462 Training 2014.0 Q1 7 331.714286 300.0 2322.0
463 Training 2014.0 Q2 12 3575.750000 291.0 42909.0
464 Training 2014.0 Q3 12 3553.583333 238.5 42643.0
465 Training 2014.0 Q4 14 1313.500000 480.0 18389.0
466 Training 2015.0 Q1 13 969.000000 252.0 12597.0
467 Training 2015.0 Q2 13 8391.846154 400.0 109094.0
468 Training 2015.0 Q3 22 8282.454545 302.0 182214.0
469 Training 2015.0 Q4 18 8539.111111 1187.0 153704.0
470 Training 2016.0 Q1 38 9757.789474 5163.0 370796.0
471 Training 2016.0 Q2 28 30924.250000 12546.0 865879.0
472 Training 2016.0 Q3 37 21737.513514 10200.0 804288.0
473 Training 2016.0 Q4 36 16919.583333 11016.0 609105.0
474 Training 2017.0 Q1 22 14963.590909 3344.0 329199.0
475 Training 2017.0 Q2 7 2100.428571 2236.0 14703.0
476 Training 2017.0 Q3 5 17724.400000 4184.0 88622.0
477 Training 2017.0 Q4 2 1304.000000 1304.0 2608.0
478 Transport Hire Charges 2014.0 Q1 1 36.000000 36.0 36.0
479 Transport Hire Charges 2014.0 Q2 1 365.000000 365.0 365.0
480 Transport Hire Charges 2014.0 Q4 1 42.000000 42.0 42.0
481 Transport Hire Charges 2015.0 Q1 1 264.000000 264.0 264.0
482 Transport Hire Charges 2015.0 Q2 2 165.000000 165.0 330.0
483 Transport Hire Charges 2016.0 Q2 1 30600.000000 30600.0 30600.0
484 Travelling Expenses 2014.0 Q1 24 1192.375000 20.0 28617.0
485 Travelling Expenses 2014.0 Q2 74 3173.324324 20.0 234826.0
486 Travelling Expenses 2014.0 Q3 71 1200.619718 23.0 85244.0
487 Travelling Expenses 2014.0 Q4 95 366.726316 20.0 34839.0
488 Travelling Expenses 2015.0 Q1 142 1505.549296 1385.0 213788.0
489 Travelling Expenses 2015.0 Q2 125 1333.344000 1385.0 166668.0
490 Travelling Expenses 2015.0 Q3 115 665.269565 245.0 76506.0
491 Travelling Expenses 2015.0 Q4 101 978.990099 102.0 98878.0
492 Travelling Expenses 2016.0 Q1 77 1703.090909 20.0 131138.0
493 Travelling Expenses 2016.0 Q2 59 2883.610169 2000.0 170133.0
494 Travelling Expenses 2016.0 Q3 62 13752.612903 2000.0 852662.0
495 Travelling Expenses 2016.0 Q4 65 3885.692308 2000.0 252570.0
496 Travelling Expenses 2017.0 Q1 66 3687.545455 2000.0 243378.0
497 Travelling Expenses 2017.0 Q2 3 18030.000000 3970.0 54090.0
498 Travelling Expenses 2017.0 Q3 3 2433.333333 2000.0 7300.0
499 Travelling Expenses 2017.0 Q4 2 2500.000000 2500.0 5000.0
500 Ttl IT & Comms 2016.0 Q3 3 8830.666667 6500.0 26492.0
501 Ttl IT & Comms 2017.0 Q1 1 2280.000000 2280.0 2280.0
502 Vehicle Running Costs 2014.0 Q1 2 9.000000 9.0 18.0
503 Vehicle Running Costs 2014.0 Q2 3 172.666667 9.0 518.0
504 Vehicle Running Costs 2014.0 Q3 6 9.000000 9.0 54.0
505 Vehicle Running Costs 2014.0 Q4 19 1237.368421 1675.0 23510.0
506 Vehicle Running Costs 2015.0 Q1 28 2079.500000 2275.0 58226.0
507 Vehicle Running Costs 2015.0 Q2 5 3517.600000 4525.0 17588.0
508 Vehicle Running Costs 2015.0 Q3 31 2382.225806 2275.0 73849.0
509 Vehicle Running Costs 2015.0 Q4 14 1836.357143 1675.0 25709.0
510 Vehicle Running Costs 2016.0 Q1 18 10561.111111 2275.0 190100.0
511 Vehicle Running Costs 2016.0 Q2 32 24603.750000 23250.0 787320.0
512 Vehicle Running Costs 2016.0 Q3 49 18660.204082 23250.0 914350.0
513 Vehicle Running Costs 2016.0 Q4 24 11437.000000 16750.0 274488.0
514 Vehicle Running Costs 2017.0 Q1 19 13006.263158 16750.0 247119.0
515 Venue Hire 2014.0 Q1 1 63.000000 63.0 63.0
516 Venue Hire 2015.0 Q1 2 196.500000 196.5 393.0
517 Venue Hire 2015.0 Q2 2 38.000000 38.0 76.0
518 Venue Hire 2015.0 Q3 1 60.000000 60.0 60.0
519 Venue Hire 2015.0 Q4 1 18.000000 18.0 18.0
520 Venue Hire 2016.0 Q1 2 217140.000000 217140.0 434280.0
521 Venue Hire 2016.0 Q3 2 62501.500000 62501.5 125003.0
522 Venue Hire 2016.0 Q4 1 41667.000000 41667.0 41667.0
523 Venue Hire 2017.0 Q1 1 15000.000000 15000.0 15000.0
524 Water Services 2015.0 Q3 2 13028.500000 13028.5 26057.0
525 Water Services 2015.0 Q4 1 2357.000000 2357.0 2357.0
526 Water Services 2016.0 Q1 1 11699.000000 11699.0 11699.0
527 Water Services 2016.0 Q2 1 18227.000000 18227.0 18227.0
528 Water Services 2016.0 Q3 1 15600.000000 15600.0 15600.0
529 Water Services 2016.0 Q4 1 3522.000000 3522.0 3522.0
530 Water Services 2017.0 Q1 3 19544.333333 24386.0 58633.0
In [45]:
#experimenting with the tables to create subset of the dataframe for plotting
In [46]:
sa_year_qrt[sa_year_qrt['Service Area']=="Adults and Communities"][['year','Quarter','count']]
Out[46]:
year Quarter count
0 2014.0 Q1 2
1 2014.0 Q2 15
2 2014.0 Q3 11
3 2014.0 Q4 14
4 2015.0 Q1 7
5 2015.0 Q2 10
6 2015.0 Q3 19
7 2015.0 Q4 17
8 2016.0 Q1 23
9 2016.0 Q2 38
10 2016.0 Q3 33
11 2016.0 Q4 36
12 2017.0 Q1 37
13 2017.0 Q2 6
14 2017.0 Q3 7
15 2017.0 Q4 3
In [47]:
sa_year_qrt[sa_year_qrt['Service Area']=="Adults and Communities"][['year','Quarter','sum']]
Out[47]:
year Quarter sum
0 2014.0 Q1 451.0
1 2014.0 Q2 71901.0
2 2014.0 Q3 2514.0
3 2014.0 Q4 49028.0
4 2015.0 Q1 7310.0
5 2015.0 Q2 147530.0
6 2015.0 Q3 75820.0
7 2015.0 Q4 24990.0
8 2016.0 Q1 194054.0
9 2016.0 Q2 750665.0
10 2016.0 Q3 480493.0
11 2016.0 Q4 667573.0
12 2017.0 Q1 500621.0
13 2017.0 Q2 15934.0
14 2017.0 Q3 48387.0
15 2017.0 Q4 359511.0
In [48]:
def plot_q1(data,col,val):
    """
    arg: data --> dataframe passed to get the plot
    arg: col --> column for which individual plots are need to be created 
    arg: val --> what kind of observation needed (mean,sum,count)
    fun: plot bar chart
    return None
    """
    count=0
    col_unique_values=data[col].unique()
    for i in col_unique_values:
        plot_df=data[data[col]==i][['year','Quarter',val]]
        count+=1
        plt.figure(figsize=(14,6))
        sns.barplot(data=plot_df,x='year',y=val,hue='Quarter')
        plt.title("{} of Transcation for {}".format(val,col))
        plt.xlabel("YEAR with unstacked Quarters")
        plt.ylabel(val)
        plt.tight_layout()
        plt.show()
In [49]:
def plot_q1a(data,col,val):
    """
    arg: data --> dataframe passed to get the plot
    arg: col --> column for summary creation
    arg: val --> column to apply sum
    fun: plot summary bar chart
    return None
    """
    summary_df = data.groupby([col, 'year', 'Quarter'])[val].sum().reset_index()
    plt.figure(figsize=(12, 8))
    a=sns.barplot(data=summary_df, x='Quarter', y=val, hue='year',errorbar=None)
    plt.title('Summary Plot: {}'.format(col))
    plt.xlabel('Quarters')
    plt.ylabel("summation of {}".format(val))
    a.yaxis.set_major_formatter(FuncFormatter(lambda x, _: format(int(x), ',')))
    plt.tight_layout()
    plt.show()

Question 1 part 1¶

Vizualization of transaction stats for each service area¶

In [50]:
plot_q1(sa_year_qrt,'Service Area','sum') #passing service area column for stats

Question 1 part 1¶

Vizualization of transaction stats for each accounts¶

In [51]:
plot_q1(ad_year_qt,'Account Description','sum') #passing account column for stats

Question 1 final summary¶

In [52]:
plot_q1a(df,'Service Area','JV Value')
In [53]:
plot_q1a(df,'Account Description','JV Value')

+++++++++++++++++++++++++++++++++++++++++++++++++

Question 2 part 1¶

creating plot and observing spike on various time sectors¶

In [54]:
#created few more tables for easy plotting 

df_filtered = df.dropna(subset=['QT_validation'])
df_filtered['Quarter'] = df_filtered['Transaction Date'].dt.to_period('Q').astype(str) #the formart was creating issue so converted 
## custom list of year-quarter
quarter_order = ['2014Q1', '2014Q2', '2014Q3', '2014Q4','2015Q1', '2015Q2', '2015Q3', '2015Q4','2016Q1', '2016Q2', '2016Q3', '2016Q4','2017Q1', '2017Q2', '2017Q3', '2017Q4']
df_filtered['Quarter'] = pd.Categorical(df_filtered['Quarter'], categories=quarter_order, ordered=True) #to control the order issue araised
In [55]:
#plotting the transaction amount 

def spike_plot(data,x,y,col_temp=None):
    """
    arg:data--> dataframe 
    arg:xaxis value
    arg:yaxis value
    col_temp: col parameter for relplot
    """
    if col_temp:
        g = sns.relplot(data=data, x=x, y=y, kind='line', col=col_temp,
                col_wrap=2, aspect=2, height=3, linewidth=2)
        g.set_titles('{col_name}')
        g.set_axis_labels('Quarter', 'Transaction Amount')

        # Adjust x-ticks for all subplots
        for ax in g.axes.flatten():
            ax.set_xticklabels(ax.get_xticklabels(), rotation=45, ha='right')

        plt.tight_layout()
        plt.show()
    else:
        sns.relplot(data=data, x=x, y=y, kind='line',height=6, linewidth=2,aspect=3,ci=None)
        plt.title('Transaction Amount')
        plt.xlabel('Quarter-Year')
        plt.ylabel('Transaction Amount')
        plt.xticks(rotation=45)
        plt.tight_layout()
        plt.show()
In [56]:
spike_plot(df,'Transaction Date','JV Value')

Question 2 part 1¶

Service Area¶

In [57]:
spike_plot(df_filtered,'Quarter','JV Value',col_temp='Service Area')
In [58]:
spike_plot(df_filtered,'year','JV Value')
In [59]:
spike_plot(df_filtered,'Quarter','JV Value')

Question 2 part 1¶

Account¶

In [60]:
spike_plot(df_filtered,'Quarter','JV Value',col_temp='Account Description')
In [61]:
#observation through z-score and percentage differnce in the transaction amount
In [62]:
sp_const=5
per_const=2
In [63]:
df['zscore'] = df.groupby(['Service Area', 'Account Description', 'QT_validation'])['JV Value'].transform(lambda x: (x - x.mean()) / x.std())
#artbitary value of 5 as treshold
z_score_spike=df[df['zscore']>sp_const]
z_score_non_spike=df[df['zscore']<per_const]

#using percentage differnce bwt consecutive values
#first date is ordered in ascending
df_sorted = df.groupby(['Service Area', 'Account Description']).apply(lambda x: x.sort_values('Transaction Date')).reset_index(drop=True)
df_sorted['%change'] = df_sorted.groupby(['Service Area', 'Account Description'])['JV Value'].pct_change()


percent_spike=df_sorted[df_sorted['%change']>sp_const]
percent_non_spike=df_sorted[df_sorted['%change']<per_const]

Question 2 part 2¶

segmenting spike and permanent changes¶

In [64]:
z_score_spike[z_score_spike["Service Area"]=="Childrens Services"][['JV Value','Service Area','zscore']].head()
Out[64]:
JV Value Service Area zscore
322 32943.0 Childrens Services 5.207716
494 68664.0 Childrens Services 5.493995
500 196000.0 Childrens Services 6.986634
673 12999.0 Childrens Services 7.431291
758 45672.0 Childrens Services 6.695405
In [65]:
percent_spike[percent_spike["Service Area"]=="Childrens Services"][['JV Value','Service Area','%change']].head()
Out[65]:
JV Value Service Area %change
9278 5997.0 Childrens Services 855.714286
9280 2274.0 Childrens Services 5.497143
9285 433.0 Childrens Services 47.111111
9286 6944.0 Childrens Services 15.036952
9290 29789.0 Childrens Services 11.991278
In [66]:
percent_non_spike[percent_non_spike["Service Area"]=="Childrens Services"][['JV Value','Service Area','%change']].head()
Out[66]:
JV Value Service Area %change
9275 222.0 Childrens Services -0.555110
9277 7.0 Childrens Services -0.989781
9279 350.0 Childrens Services -0.941637
9281 249.0 Childrens Services -0.890501
9282 252.0 Childrens Services 0.012048
In [67]:
z_score_non_spike[z_score_non_spike["Service Area"]=="Childrens Services"][['JV Value','Service Area','zscore']].head()
Out[67]:
JV Value Service Area zscore
1 600000.0 Childrens Services 1.788670
2 30938.0 Childrens Services 0.707107
3 21876.0 Childrens Services -0.707107
4 306.0 Childrens Services -0.356084
5 732.0 Childrens Services -0.471575

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Question 4¶

similarity I¶

In [68]:
#creating pipeline for clustering the service area by transaction count and total
In [69]:
def similarity(data,col,val,k):
    """
    arg:data--> dataframe 
    arg:col--> column which has to be clustered 
    arg:val--> to be clustered of
    arg:k--> cluster number for kmeans
    function: to create a clustering model
    returns dataframe with culster number 
    """
    temp=data.groupby(col)[val].agg(['count', 'sum', 'mean', 'median', 'std']).reset_index()
    temp=temp.dropna()
#     temp = temp.replace([np.inf, -np.inf], np.nan).dropna()
    std= StandardScaler()
    scaled=std.fit_transform(temp.drop(col, axis=1))
    kmeans = KMeans(n_clusters=k)
    kmeans.fit(scaled)
    temp['cluster_id']=kmeans.labels_
    
    plt.figure(figsize=(14,7))
    sns.scatterplot(data=temp,x='sum',y='mean',hue='cluster_id',palette='viridis',s=100)
    plt.legend()
    plt.title("similarity plot")
    plt.grid(True)
    plt.show()
    
    
#     plt.figure(figsize=(10, 6))
# sns.scatterplot(x='sum', y='sum', hue='cluster_id', data=a, palette='viridis', s=100)
# plt.title('Service Areas Grouped by Spending Behavior')
# plt.xlabel('Average Transaction Amount')
# plt.ylabel('Total Transaction Amount')
# plt.legend(title='Cluster')
# plt.grid(True)
# plt.show()
    return temp,scaled
In [70]:
def hac(data,scale,link,col):
    """
    arg: data--> dataframe
    arg: scaled--> scaled numeric value
    arg: linkage method 
    arg:leaves of dendogram by which column
    functon: creating HAC plot
    return: None
    """
    cal_temp=linkage(scale,method=link)
    plt.figure(figsize=(14,7))
    dendrogram(cal_temp,labels=data[col].values,leaf_rotation=90)
    plt.title('HAC')
    plt.xlabel(col)
    plt.show()
In [ ]:
In [71]:
clus_analysis,scaled=similarity(df,'Service Area','JV Value',3)
In [72]:
clus_analysis
Out[72]:
Service Area count sum mean median std cluster_id
0 Adults and Communities 278 3396782.0 12218.640288 1831.0 36217.416042 0
1 Assurance 344 1829415.0 5318.066860 900.0 27137.891992 0
2 CSG Managed Budget 36 4290803.0 119188.972222 4564.0 213468.294515 1
3 Children's Education & Skills 667 4128956.0 6190.338831 1385.0 12512.615280 0
4 Children's Family Services 7672 44773136.0 5835.914494 1797.5 12028.966756 2
5 Children's Service DSG 277 1757715.0 6345.541516 1894.0 13725.713997 0
6 Childrens Services 1297 6194487.0 4776.011565 675.0 20421.416626 0
7 Commercial 9 207786.0 23087.333333 594.0 37508.583144 0
8 Commissioning 400 5934935.0 14837.337500 4612.0 37451.003617 0
9 Control Accounts 8 19071.0 2383.875000 813.5 3146.298616 0
10 Customer Support Group 117 16204913.0 138503.529915 10399.0 267847.540772 1
11 Deputy Chief Operating Officer 113 170580.0 1509.557522 121.0 2830.648966 0
12 Education 95 453424.0 4772.884211 500.0 9874.088276 0
13 Family Services 770 2825727.0 3669.775325 638.5 8419.613334 0
14 Governance 8 1376694.0 172086.750000 24943.5 288737.362988 1
17 Internal Audit & CAFT 11 13880.0 1261.818182 99.0 2097.858042 0
18 NSCSO 3 1765.0 588.333333 300.0 764.433341 0
19 Parking & Infrastructure 12 462248.0 38520.666667 7209.5 78889.695790 0
20 Public Health 3 1683.0 561.000000 455.0 489.681529 0
21 Regional Enterprise 6 216960.0 36160.000000 14300.0 63677.217276 0
23 Street Scene 39 92540.0 2372.820513 962.0 3710.534547 0
24 Streetscene 420 4545203.0 10821.911905 3691.0 17268.899639 0
In [73]:
clus_analysis[clus_analysis['cluster_id']==0]
Out[73]:
Service Area count sum mean median std cluster_id
0 Adults and Communities 278 3396782.0 12218.640288 1831.0 36217.416042 0
1 Assurance 344 1829415.0 5318.066860 900.0 27137.891992 0
3 Children's Education & Skills 667 4128956.0 6190.338831 1385.0 12512.615280 0
5 Children's Service DSG 277 1757715.0 6345.541516 1894.0 13725.713997 0
6 Childrens Services 1297 6194487.0 4776.011565 675.0 20421.416626 0
7 Commercial 9 207786.0 23087.333333 594.0 37508.583144 0
8 Commissioning 400 5934935.0 14837.337500 4612.0 37451.003617 0
9 Control Accounts 8 19071.0 2383.875000 813.5 3146.298616 0
11 Deputy Chief Operating Officer 113 170580.0 1509.557522 121.0 2830.648966 0
12 Education 95 453424.0 4772.884211 500.0 9874.088276 0
13 Family Services 770 2825727.0 3669.775325 638.5 8419.613334 0
17 Internal Audit & CAFT 11 13880.0 1261.818182 99.0 2097.858042 0
18 NSCSO 3 1765.0 588.333333 300.0 764.433341 0
19 Parking & Infrastructure 12 462248.0 38520.666667 7209.5 78889.695790 0
20 Public Health 3 1683.0 561.000000 455.0 489.681529 0
21 Regional Enterprise 6 216960.0 36160.000000 14300.0 63677.217276 0
23 Street Scene 39 92540.0 2372.820513 962.0 3710.534547 0
24 Streetscene 420 4545203.0 10821.911905 3691.0 17268.899639 0

Question 4¶

similarity II¶

In [74]:
hac(clus_analysis,scaled,'ward','Service Area')

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Question 3¶

In [75]:
def repeating_values(data,check_col,on_col,repeat):
    """
    arg:data-->dataframe
    arg:check_col-->column to be looked for misclassifications
    arg:on_col-->against which column
    arg:repeat--> repetation allowed
    function: calculating the frequency of the column values
    return: dataframe
    """
    grp=data.groupby([check_col,on_col]).size().reset_index(name="count")
    frq=grp.groupby(check_col)[on_col].count()
    repeating_cond = frq[frq > 1]
    top_inst=repeating_cond.sort_values(ascending=False)[:25]
        
    plt.figure(figsize=(14,7))
    plt.bar(top_inst.index,top_inst.values)
    plt.xlabel('creditor name')
    plt.ylabel('number of accounts present in')
    plt.xticks(rotation=90)
    plt.tight_layout()
    plt.show()
    
    return top_inst,repeating_cond
In [76]:
top_list,whole_list=repeating_values(df,'Creditor','Account Description',2)
In [77]:
top_list.head()
Out[77]:
Creditor
AMAZON UK MARKETPLACE     25
AMAZON EU                 18
AMAZON UK RETAIL AMAZO    15
AMAZON SVCS EUROPE,SAR    14
ASDA SUPERSTORE           13
Name: Account Description, dtype: int64
In [78]:
whole_list.shape[0] #total of 474 creditors have been mapped into 3 or more accounts
Out[78]:
474

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

In [79]:
##anomaly detection
In [80]:
#creating dataframe based on weekly transaction sum
def anamoly(data,date_col,grp_col,val_col):
    """
    arg:data--> dataframe to be checked for anomaly
    arg:date_col--> considering anomaly has strong correlation with time ; passing the required data
    arg:grp_col-->which column is checked for the anomaly
    arg:val_col--> which column is used to check the anomaly
    func: basic plots of the anomaly in the transactions weekly
    return: supported dataframe
    """
    data[date_col] = pd.to_datetime(data[date_col])
    data[val_col]=pd.to_numeric(df2[val_col],errors='coerce')
    temp_grp=data.groupby([grp_col,data[date_col].dt.week])
    weekly_values=temp_grp[val_col].sum().reset_index() #
    treshold=2 * weekly_values.groupby(grp_col)[val_col].transform('std') #
    week_anomaly=weekly_values.copy()
    anomaly=week_anomaly[week_anomaly[val_col] > treshold]
    week_anomaly['Anomaly']=week_anomaly[val_col]>treshold#
#     week_anomaly['Anomaly']=week_anomaly[val_col]>threshold#

    for service_area,grp in week_anomaly.groupby(grp_col):
        an_temp=grp[val_col]>treshold[grp.index]
        plt.figure(figsize=(14,7))
        plt.plot(grp[date_col], grp[val_col], label='Normal Transactions', color='blue')
        plt.scatter(grp.loc[an_temp,date_col],grp.loc[an_temp,val_col],color='red',label='Anomalies')
        plt.title(service_area)
        plt.legend()
        plt.grid(True)
        plt.show()

            
    return weekly_values,week_anomaly,anomaly,treshold

Question 5 part 1¶

ploting values of abnormal transactions in service area / week¶

In [81]:
weekly_trans_sum,anomaly_encoded_df,only_anomaly,treshold=anamoly(df,'Transaction Date','Service Area','JV Value')

Question 5 part 2¶

service area transaction sum over week (consective )¶

In [82]:
weekly_trans_sum
Out[82]:
Service Area Transaction Date JV Value
0 Adults and Communities 1.0 0.00
1 Adults and Communities 2.0 36.85
2 Adults and Communities 3.0 42.50
3 Adults and Communities 4.0 0.00
4 Adults and Communities 5.0 13.85
5 Adults and Communities 6.0 41.93
6 Adults and Communities 7.0 0.00
7 Adults and Communities 8.0 0.00
8 Adults and Communities 9.0 0.00
9 Adults and Communities 10.0 0.00
10 Adults and Communities 11.0 0.00
11 Adults and Communities 12.0 0.00
12 Adults and Communities 13.0 0.00
13 Adults and Communities 14.0 0.00
14 Adults and Communities 15.0 5.35
15 Adults and Communities 16.0 28.66
16 Adults and Communities 17.0 0.00
17 Adults and Communities 18.0 61.04
18 Adults and Communities 19.0 67.83
19 Adults and Communities 20.0 8.03
20 Adults and Communities 21.0 0.00
21 Adults and Communities 22.0 64.38
22 Adults and Communities 23.0 0.00
23 Adults and Communities 24.0 20.00
24 Adults and Communities 25.0 0.05
25 Adults and Communities 26.0 -3.02
26 Adults and Communities 27.0 242.46
27 Adults and Communities 28.0 0.00
28 Adults and Communities 29.0 0.00
29 Adults and Communities 30.0 0.00
30 Adults and Communities 31.0 109.40
31 Adults and Communities 32.0 20.00
32 Adults and Communities 33.0 74.75
33 Adults and Communities 34.0 431.05
34 Adults and Communities 35.0 0.00
35 Adults and Communities 36.0 113.05
36 Adults and Communities 37.0 27.50
37 Adults and Communities 38.0 16.67
38 Adults and Communities 39.0 0.00
39 Adults and Communities 40.0 0.00
40 Adults and Communities 41.0 1079.50
41 Adults and Communities 42.0 20.00
42 Adults and Communities 43.0 20.00
43 Adults and Communities 44.0 32.05
44 Adults and Communities 45.0 26.12
45 Adults and Communities 46.0 125.01
46 Adults and Communities 47.0 0.00
47 Adults and Communities 48.0 103.90
48 Adults and Communities 49.0 56.50
49 Adults and Communities 50.0 0.00
50 Adults and Communities 51.0 5.00
51 Adults and Communities 52.0 18.00
52 Adults and Communities 53.0 0.00
53 Assurance 1.0 0.00
54 Assurance 2.0 0.00
55 Assurance 3.0 0.00
56 Assurance 4.0 13.85
57 Assurance 5.0 39.85
58 Assurance 6.0 0.00
59 Assurance 7.0 0.00
60 Assurance 8.0 0.00
61 Assurance 9.0 0.00
62 Assurance 10.0 0.00
63 Assurance 11.0 0.00
64 Assurance 12.0 0.00
65 Assurance 13.0 0.00
66 Assurance 14.0 0.00
67 Assurance 15.0 0.00
68 Assurance 16.0 0.00
69 Assurance 17.0 0.00
70 Assurance 18.0 0.00
71 Assurance 19.0 0.00
72 Assurance 20.0 0.00
73 Assurance 21.0 0.00
74 Assurance 22.0 0.00
75 Assurance 23.0 0.00
76 Assurance 24.0 7.30
77 Assurance 25.0 0.00
78 Assurance 26.0 0.00
79 Assurance 27.0 0.00
80 Assurance 28.0 0.00
81 Assurance 29.0 0.00
82 Assurance 30.0 0.00
83 Assurance 31.0 0.00
84 Assurance 32.0 0.00
85 Assurance 33.0 0.00
86 Assurance 34.0 0.00
87 Assurance 35.0 0.00
88 Assurance 36.0 0.00
89 Assurance 37.0 0.00
90 Assurance 38.0 0.00
91 Assurance 39.0 0.00
92 Assurance 40.0 0.00
93 Assurance 41.0 0.00
94 Assurance 42.0 0.00
95 Assurance 43.0 0.00
96 Assurance 44.0 0.00
97 Assurance 45.0 0.00
98 Assurance 46.0 83.25
99 Assurance 47.0 -1.45
100 Assurance 48.0 0.00
101 Assurance 49.0 0.00
102 Assurance 50.0 0.00
103 Assurance 51.0 0.00
104 Assurance 52.0 0.00
105 Assurance 53.0 0.00
106 CSG Managed Budget 2.0 182.08
107 CSG Managed Budget 6.0 418.46
108 CSG Managed Budget 19.0 30.50
109 CSG Managed Budget 20.0 13.40
110 CSG Managed Budget 21.0 147.18
111 CSG Managed Budget 22.0 84.08
112 CSG Managed Budget 25.0 3.47
113 CSG Managed Budget 28.0 106.87
114 CSG Managed Budget 30.0 150.54
115 CSG Managed Budget 31.0 96.20
116 CSG Managed Budget 34.0 56.05
117 CSG Managed Budget 36.0 12.52
118 CSG Managed Budget 39.0 20.00
119 CSG Managed Budget 42.0 50.00
120 CSG Managed Budget 43.0 78.90
121 CSG Managed Budget 49.0 157.06
122 Children's Education & Skills 2.0 0.00
123 Children's Education & Skills 3.0 981.49
124 Children's Education & Skills 4.0 1238.16
125 Children's Education & Skills 5.0 549.83
126 Children's Education & Skills 6.0 0.00
127 Children's Education & Skills 7.0 237.74
128 Children's Education & Skills 8.0 4.50
129 Children's Education & Skills 9.0 0.00
130 Children's Education & Skills 10.0 0.00
131 Children's Education & Skills 11.0 33.00
132 Children's Education & Skills 14.0 0.00
133 Children's Education & Skills 15.0 852.28
134 Children's Education & Skills 16.0 0.00
135 Children's Education & Skills 18.0 227.50
136 Children's Education & Skills 19.0 5.00
137 Children's Education & Skills 20.0 160.71
138 Children's Education & Skills 21.0 0.00
139 Children's Education & Skills 22.0 0.00
140 Children's Education & Skills 23.0 299.64
141 Children's Education & Skills 24.0 77.47
142 Children's Education & Skills 25.0 0.00
143 Children's Education & Skills 26.0 0.00
144 Children's Education & Skills 27.0 558.32
145 Children's Education & Skills 28.0 69.50
146 Children's Education & Skills 29.0 0.00
147 Children's Education & Skills 30.0 0.00
148 Children's Education & Skills 31.0 85.98
149 Children's Education & Skills 32.0 0.00
150 Children's Education & Skills 33.0 100.50
151 Children's Education & Skills 34.0 0.00
152 Children's Education & Skills 35.0 0.00
153 Children's Education & Skills 36.0 173.94
154 Children's Education & Skills 37.0 74.13
155 Children's Education & Skills 38.0 0.00
156 Children's Education & Skills 39.0 0.00
157 Children's Education & Skills 40.0 0.00
158 Children's Education & Skills 41.0 503.48
159 Children's Education & Skills 42.0 0.00
160 Children's Education & Skills 43.0 0.00
161 Children's Education & Skills 44.0 0.00
162 Children's Education & Skills 45.0 350.00
163 Children's Education & Skills 46.0 446.98
164 Children's Education & Skills 47.0 485.47
165 Children's Education & Skills 48.0 1121.65
166 Children's Education & Skills 49.0 629.66
167 Children's Education & Skills 50.0 202.41
168 Children's Education & Skills 51.0 1441.11
169 Children's Education & Skills 52.0 6.98
170 Children's Education & Skills 53.0 0.00
171 Children's Family Services 1.0 957.04
172 Children's Family Services 2.0 1601.09
173 Children's Family Services 3.0 9094.78
174 Children's Family Services 4.0 6305.44
175 Children's Family Services 5.0 10119.26
176 Children's Family Services 6.0 3211.83
177 Children's Family Services 7.0 1943.75
178 Children's Family Services 8.0 2735.92
179 Children's Family Services 9.0 3095.46
180 Children's Family Services 10.0 320.75
181 Children's Family Services 11.0 95.83
182 Children's Family Services 12.0 0.00
183 Children's Family Services 13.0 0.00
184 Children's Family Services 14.0 1261.34
185 Children's Family Services 15.0 1944.05
186 Children's Family Services 16.0 0.00
187 Children's Family Services 17.0 0.00
188 Children's Family Services 18.0 2040.11
189 Children's Family Services 19.0 50.89
190 Children's Family Services 20.0 660.31
191 Children's Family Services 21.0 0.00
192 Children's Family Services 22.0 0.00
193 Children's Family Services 23.0 2530.23
194 Children's Family Services 24.0 810.14
195 Children's Family Services 25.0 0.00
196 Children's Family Services 26.0 0.00
197 Children's Family Services 27.0 1077.74
198 Children's Family Services 28.0 673.90
199 Children's Family Services 29.0 0.00
200 Children's Family Services 30.0 0.00
201 Children's Family Services 31.0 857.39
202 Children's Family Services 32.0 0.00
203 Children's Family Services 33.0 986.25
204 Children's Family Services 34.0 0.00
205 Children's Family Services 35.0 0.00
206 Children's Family Services 36.0 2701.74
207 Children's Family Services 37.0 619.87
208 Children's Family Services 38.0 0.00
209 Children's Family Services 39.0 0.00
210 Children's Family Services 40.0 314.57
211 Children's Family Services 41.0 2248.31
212 Children's Family Services 42.0 0.00
213 Children's Family Services 43.0 0.00
214 Children's Family Services 44.0 0.00
215 Children's Family Services 45.0 114.17
216 Children's Family Services 46.0 1897.92
217 Children's Family Services 47.0 7360.60
218 Children's Family Services 48.0 5911.19
219 Children's Family Services 49.0 2234.96
220 Children's Family Services 50.0 1154.23
221 Children's Family Services 51.0 4995.83
222 Children's Family Services 52.0 2433.27
223 Children's Family Services 53.0 0.00
224 Children's Service DSG 2.0 128.90
225 Children's Service DSG 3.0 0.00
226 Children's Service DSG 4.0 0.00
227 Children's Service DSG 5.0 13.85
228 Children's Service DSG 6.0 739.75
229 Children's Service DSG 7.0 124.71
230 Children's Service DSG 8.0 0.00
231 Children's Service DSG 9.0 0.00
232 Children's Service DSG 10.0 0.00
233 Children's Service DSG 11.0 180.45
234 Children's Service DSG 12.0 0.00
235 Children's Service DSG 13.0 0.00
236 Children's Service DSG 14.0 0.00
237 Children's Service DSG 15.0 20.00
238 Children's Service DSG 16.0 0.00
239 Children's Service DSG 17.0 0.00
240 Children's Service DSG 18.0 0.00
241 Children's Service DSG 19.0 303.77
242 Children's Service DSG 20.0 15.77
243 Children's Service DSG 21.0 0.00
244 Children's Service DSG 22.0 0.00
245 Children's Service DSG 23.0 64.48
246 Children's Service DSG 24.0 127.30
247 Children's Service DSG 25.0 0.00
248 Children's Service DSG 26.0 0.00
249 Children's Service DSG 27.0 0.00
250 Children's Service DSG 28.0 79.98
251 Children's Service DSG 29.0 0.00
252 Children's Service DSG 30.0 0.00
253 Children's Service DSG 31.0 41.98
254 Children's Service DSG 32.0 0.00
255 Children's Service DSG 33.0 578.61
256 Children's Service DSG 34.0 252.05
257 Children's Service DSG 35.0 33.00
258 Children's Service DSG 36.0 252.96
259 Children's Service DSG 37.0 298.16
260 Children's Service DSG 38.0 8.15
261 Children's Service DSG 39.0 365.93
262 Children's Service DSG 40.0 0.00
263 Children's Service DSG 42.0 194.32
264 Children's Service DSG 43.0 192.20
265 Children's Service DSG 44.0 40.00
266 Children's Service DSG 45.0 0.00
267 Children's Service DSG 46.0 101.28
268 Children's Service DSG 47.0 14.37
269 Children's Service DSG 48.0 572.87
270 Children's Service DSG 49.0 0.00
271 Children's Service DSG 50.0 0.00
272 Children's Service DSG 51.0 96.39
273 Children's Service DSG 53.0 0.00
274 Childrens Services 1.0 273.65
275 Childrens Services 2.0 2893.47
276 Childrens Services 6.0 4036.87
277 Childrens Services 7.0 20.00
278 Childrens Services 10.0 2199.33
279 Childrens Services 11.0 20.00
280 Childrens Services 14.0 1229.29
281 Childrens Services 15.0 1057.00
282 Childrens Services 16.0 1828.23
283 Childrens Services 17.0 2919.19
284 Childrens Services 18.0 4870.81
285 Childrens Services 19.0 351.56
286 Childrens Services 20.0 5040.35
287 Childrens Services 21.0 3705.73
288 Childrens Services 22.0 4909.91
289 Childrens Services 23.0 548.94
290 Childrens Services 24.0 403.48
291 Childrens Services 25.0 3229.64
292 Childrens Services 26.0 1890.47
293 Childrens Services 27.0 2099.33
294 Childrens Services 28.0 2417.57
295 Childrens Services 29.0 5472.17
296 Childrens Services 30.0 7962.97
297 Childrens Services 31.0 3169.25
298 Childrens Services 32.0 3104.53
299 Childrens Services 36.0 2191.71
300 Childrens Services 40.0 938.28
301 Childrens Services 41.0 1287.93
302 Childrens Services 42.0 289.17
303 Childrens Services 43.0 167.60
304 Childrens Services 44.0 52.00
305 Childrens Services 45.0 1865.51
306 Childrens Services 49.0 1233.81
307 Commercial 19.0 114.98
308 Commercial 32.0 12.00
309 Commercial 34.0 217.75
310 Commercial 35.0 374.86
311 Commercial 39.0 173.60
312 Commissioning 1.0 204.56
313 Commissioning 2.0 60.60
314 Commissioning 3.0 57.19
315 Commissioning 4.0 43.95
316 Commissioning 5.0 296.29
317 Commissioning 6.0 0.00
318 Commissioning 7.0 41.67
319 Commissioning 8.0 4.50
320 Commissioning 9.0 0.00
321 Commissioning 10.0 0.00
322 Commissioning 11.0 206.60
323 Commissioning 12.0 0.00
324 Commissioning 14.0 0.00
325 Commissioning 15.0 8.20
326 Commissioning 16.0 0.00
327 Commissioning 17.0 0.00
328 Commissioning 18.0 227.50
329 Commissioning 19.0 0.00
330 Commissioning 20.0 295.00
331 Commissioning 21.0 0.00
332 Commissioning 22.0 0.00
333 Commissioning 23.0 2.50
334 Commissioning 24.0 0.00
335 Commissioning 25.0 0.00
336 Commissioning 26.0 0.00
337 Commissioning 27.0 0.00
338 Commissioning 28.0 11.50
339 Commissioning 29.0 0.00
340 Commissioning 30.0 0.00
341 Commissioning 31.0 23.64
342 Commissioning 32.0 0.00
343 Commissioning 33.0 125.04
344 Commissioning 34.0 0.00
345 Commissioning 35.0 0.00
346 Commissioning 36.0 17.04
347 Commissioning 37.0 0.00
348 Commissioning 38.0 0.00
349 Commissioning 39.0 0.00
350 Commissioning 40.0 0.00
351 Commissioning 41.0 883.22
352 Commissioning 42.0 0.00
353 Commissioning 43.0 0.00
354 Commissioning 44.0 0.00
355 Commissioning 45.0 38.79
356 Commissioning 46.0 146.60
357 Commissioning 47.0 119.62
358 Commissioning 48.0 420.00
359 Commissioning 49.0 0.00
360 Commissioning 50.0 34.86
361 Commissioning 51.0 398.20
362 Commissioning 52.0 123.42
363 Commissioning 53.0 0.00
364 Control Accounts 10.0 653.84
365 Control Accounts 14.0 909.28
366 Control Accounts 24.0 12.50
367 Control Accounts 27.0 268.86
368 Customer Support Group 2.0 0.00
369 Customer Support Group 3.0 17.04
370 Customer Support Group 4.0 1159.10
371 Customer Support Group 5.0 0.00
372 Customer Support Group 7.0 13.39
373 Customer Support Group 8.0 0.00
374 Customer Support Group 9.0 0.00
375 Customer Support Group 10.0 0.00
376 Customer Support Group 12.0 0.00
377 Customer Support Group 13.0 0.00
378 Customer Support Group 15.0 19.25
379 Customer Support Group 16.0 0.00
380 Customer Support Group 17.0 0.00
381 Customer Support Group 20.0 22.92
382 Customer Support Group 21.0 0.00
383 Customer Support Group 22.0 0.00
384 Customer Support Group 23.0 0.00
385 Customer Support Group 24.0 23.00
386 Customer Support Group 25.0 0.00
387 Customer Support Group 26.0 0.00
388 Customer Support Group 27.0 0.00
389 Customer Support Group 28.0 0.00
390 Customer Support Group 29.0 0.00
391 Customer Support Group 30.0 0.00
392 Customer Support Group 31.0 17.04
393 Customer Support Group 32.0 0.00
394 Customer Support Group 33.0 0.00
395 Customer Support Group 34.0 0.00
396 Customer Support Group 35.0 0.00
397 Customer Support Group 36.0 96.79
398 Customer Support Group 39.0 0.00
399 Customer Support Group 41.0 6.58
400 Customer Support Group 43.0 0.00
401 Customer Support Group 45.0 0.00
402 Customer Support Group 46.0 52.51
403 Customer Support Group 47.0 187.43
404 Customer Support Group 48.0 0.00
405 Customer Support Group 49.0 0.00
406 Customer Support Group 51.0 165.40
407 Customer Support Group 52.0 0.00
408 Deputy Chief Operating Officer 2.0 344.94
409 Deputy Chief Operating Officer 6.0 19.17
410 Deputy Chief Operating Officer 10.0 136.25
411 Deputy Chief Operating Officer 11.0 20.00
412 Deputy Chief Operating Officer 14.0 135.17
413 Deputy Chief Operating Officer 15.0 1279.90
414 Deputy Chief Operating Officer 17.0 77.13
415 Deputy Chief Operating Officer 18.0 8.32
416 Deputy Chief Operating Officer 19.0 149.87
417 Deputy Chief Operating Officer 20.0 29.98
418 Deputy Chief Operating Officer 21.0 128.21
419 Deputy Chief Operating Officer 22.0 957.20
420 Deputy Chief Operating Officer 24.0 20.00
421 Deputy Chief Operating Officer 25.0 125.06
422 Deputy Chief Operating Officer 27.0 61.55
423 Deputy Chief Operating Officer 29.0 2240.37
424 Deputy Chief Operating Officer 30.0 221.45
425 Deputy Chief Operating Officer 32.0 238.69
426 Deputy Chief Operating Officer 33.0 -0.26
427 Deputy Chief Operating Officer 34.0 123.22
428 Deputy Chief Operating Officer 35.0 52.50
429 Deputy Chief Operating Officer 36.0 94.68
430 Deputy Chief Operating Officer 38.0 1073.91
431 Deputy Chief Operating Officer 39.0 534.63
432 Deputy Chief Operating Officer 40.0 319.94
433 Deputy Chief Operating Officer 41.0 154.60
434 Deputy Chief Operating Officer 42.0 354.67
435 Deputy Chief Operating Officer 43.0 238.61
436 Deputy Chief Operating Officer 44.0 20.00
437 Deputy Chief Operating Officer 45.0 114.90
438 Deputy Chief Operating Officer 49.0 39.37
439 Deputy Chief Operating Officer 50.0 180.00
440 Education 2.0 503.52
441 Education 6.0 349.76
442 Education 7.0 213.12
443 Education 10.0 419.78
444 Education 11.0 450.24
445 Education 24.0 84.00
446 Education 28.0 66.91
447 Education 32.0 1164.33
448 Education 33.0 510.10
449 Education 34.0 664.44
450 Education 35.0 260.07
451 Education 37.0 871.36
452 Education 38.0 252.00
453 Education 39.0 1531.51
454 Education 40.0 158.07
455 Education 41.0 50.51
456 Education 42.0 1080.05
457 Education 43.0 323.89
458 Education 44.0 845.36
459 Education 45.0 8.41
460 Education 50.0 153.00
461 Family Services 2.0 3053.45
462 Family Services 6.0 621.65
463 Family Services 7.0 973.66
464 Family Services 10.0 -970.08
465 Family Services 11.0 1968.27
466 Family Services 15.0 2650.57
467 Family Services 19.0 1554.72
468 Family Services 23.0 533.99
469 Family Services 24.0 214.98
470 Family Services 28.0 1991.99
471 Family Services 32.0 5492.32
472 Family Services 33.0 4443.37
473 Family Services 34.0 7192.16
474 Family Services 35.0 3670.33
475 Family Services 37.0 3039.46
476 Family Services 38.0 3918.12
477 Family Services 39.0 6926.76
478 Family Services 40.0 2165.64
479 Family Services 41.0 973.05
480 Family Services 42.0 5405.30
481 Family Services 43.0 3497.24
482 Family Services 44.0 4693.72
483 Family Services 45.0 2515.27
484 Family Services 50.0 2363.03
485 Governance 2.0 -7.50
486 Governance 17.0 71.74
487 Governance 21.0 88.99
488 Governance 26.0 26.98
489 Governance 39.0 20.00
490 Governance 43.0 20.00
491 Governance 50.0 20.00
492 HRA 47.0 0.00
493 Internal Audit & CAFT 2.0 6.55
494 Internal Audit & CAFT 15.0 90.45
495 Internal Audit & CAFT 25.0 -0.40
496 Internal Audit & CAFT 29.0 109.46
497 Internal Audit & CAFT 30.0 45.00
498 Internal Audit & CAFT 38.0 40.00
499 Internal Audit & CAFT 39.0 294.00
500 Internal Audit & CAFT 40.0 82.84
501 Internal Audit & CAFT 44.0 3.50
502 NSCSO 16.0 0.93
503 NSCSO 29.0 130.00
504 NSCSO 30.0 4.50
505 Parking & Infrastructure 2.0 0.00
506 Parking & Infrastructure 7.0 0.00
507 Parking & Infrastructure 12.0 0.00
508 Parking & Infrastructure 23.0 875.42
509 Parking & Infrastructure 24.0 26.66
510 Parking & Infrastructure 30.0 0.00
511 Parking & Infrastructure 41.0 0.00
512 Parking & Infrastructure 48.0 142.84
513 Parking & Infrastructure 50.0 0.00
514 Public Health 10.0 0.19
515 Public Health 17.0 -50.69
516 Public Health 25.0 0.00
517 Regional Enterprise 3.0 0.00
518 Regional Enterprise 4.0 13.85
519 Regional Enterprise 11.0 0.00
520 Regional Enterprise 45.0 0.00
521 Regional Enterprise 48.0 0.00
522 Regional Enterprise 51.0 0.00
523 Strategic Commissioning Board 26.0 3.60
524 Street Scene 1.0 89.00
525 Street Scene 2.0 54.00
526 Street Scene 11.0 20.00
527 Street Scene 16.0 8.48
528 Street Scene 17.0 12.68
529 Street Scene 23.0 105.43
530 Street Scene 24.0 127.84
531 Street Scene 25.0 117.35
532 Street Scene 27.0 6.45
533 Street Scene 28.0 15.00
534 Street Scene 29.0 158.74
535 Street Scene 31.0 121.69
536 Street Scene 33.0 19.54
537 Street Scene 34.0 19.17
538 Street Scene 35.0 127.76
539 Street Scene 36.0 15.00
540 Street Scene 38.0 86.92
541 Street Scene 39.0 1.30
542 Street Scene 42.0 41.00
543 Street Scene 43.0 29.00
544 Street Scene 44.0 483.96
545 Street Scene 45.0 44.35
546 Street Scene 50.0 1.30
547 Streetscene 1.0 0.00
548 Streetscene 2.0 166.86
549 Streetscene 3.0 165.99
550 Streetscene 4.0 110.76
551 Streetscene 5.0 17.04
552 Streetscene 6.0 227.50
553 Streetscene 7.0 0.00
554 Streetscene 8.0 494.11
555 Streetscene 9.0 57.87
556 Streetscene 10.0 0.00
557 Streetscene 11.0 0.00
558 Streetscene 12.0 0.00
559 Streetscene 13.0 0.00
560 Streetscene 14.0 0.00
561 Streetscene 15.0 30.00
562 Streetscene 16.0 0.00
563 Streetscene 18.0 0.00
564 Streetscene 20.0 0.00
565 Streetscene 21.0 0.00
566 Streetscene 22.0 0.00
567 Streetscene 23.0 23.96
568 Streetscene 24.0 14.50
569 Streetscene 25.0 0.00
570 Streetscene 26.0 0.00
571 Streetscene 27.0 0.00
572 Streetscene 28.0 144.23
573 Streetscene 29.0 0.00
574 Streetscene 30.0 0.00
575 Streetscene 32.0 0.00
576 Streetscene 33.0 0.00
577 Streetscene 34.0 0.00
578 Streetscene 35.0 0.00
579 Streetscene 36.0 220.70
580 Streetscene 37.0 0.00
581 Streetscene 38.0 0.00
582 Streetscene 39.0 0.00
583 Streetscene 40.0 30.79
584 Streetscene 41.0 0.00
585 Streetscene 42.0 0.00
586 Streetscene 43.0 0.00
587 Streetscene 44.0 0.00
588 Streetscene 45.0 0.00
589 Streetscene 46.0 107.25
590 Streetscene 47.0 164.80
591 Streetscene 48.0 909.47
592 Streetscene 49.0 51.72
593 Streetscene 50.0 9.05
594 Streetscene 51.0 609.08
595 Streetscene 52.0 0.00

Question 5 part 3¶

anomaly encoded dataframe¶

In [83]:
anomaly_encoded_df
Out[83]:
Service Area Transaction Date JV Value Anomaly
0 Adults and Communities 1.0 0.00 False
1 Adults and Communities 2.0 36.85 False
2 Adults and Communities 3.0 42.50 False
3 Adults and Communities 4.0 0.00 False
4 Adults and Communities 5.0 13.85 False
5 Adults and Communities 6.0 41.93 False
6 Adults and Communities 7.0 0.00 False
7 Adults and Communities 8.0 0.00 False
8 Adults and Communities 9.0 0.00 False
9 Adults and Communities 10.0 0.00 False
10 Adults and Communities 11.0 0.00 False
11 Adults and Communities 12.0 0.00 False
12 Adults and Communities 13.0 0.00 False
13 Adults and Communities 14.0 0.00 False
14 Adults and Communities 15.0 5.35 False
15 Adults and Communities 16.0 28.66 False
16 Adults and Communities 17.0 0.00 False
17 Adults and Communities 18.0 61.04 False
18 Adults and Communities 19.0 67.83 False
19 Adults and Communities 20.0 8.03 False
20 Adults and Communities 21.0 0.00 False
21 Adults and Communities 22.0 64.38 False
22 Adults and Communities 23.0 0.00 False
23 Adults and Communities 24.0 20.00 False
24 Adults and Communities 25.0 0.05 False
25 Adults and Communities 26.0 -3.02 False
26 Adults and Communities 27.0 242.46 False
27 Adults and Communities 28.0 0.00 False
28 Adults and Communities 29.0 0.00 False
29 Adults and Communities 30.0 0.00 False
30 Adults and Communities 31.0 109.40 False
31 Adults and Communities 32.0 20.00 False
32 Adults and Communities 33.0 74.75 False
33 Adults and Communities 34.0 431.05 True
34 Adults and Communities 35.0 0.00 False
35 Adults and Communities 36.0 113.05 False
36 Adults and Communities 37.0 27.50 False
37 Adults and Communities 38.0 16.67 False
38 Adults and Communities 39.0 0.00 False
39 Adults and Communities 40.0 0.00 False
40 Adults and Communities 41.0 1079.50 True
41 Adults and Communities 42.0 20.00 False
42 Adults and Communities 43.0 20.00 False
43 Adults and Communities 44.0 32.05 False
44 Adults and Communities 45.0 26.12 False
45 Adults and Communities 46.0 125.01 False
46 Adults and Communities 47.0 0.00 False
47 Adults and Communities 48.0 103.90 False
48 Adults and Communities 49.0 56.50 False
49 Adults and Communities 50.0 0.00 False
50 Adults and Communities 51.0 5.00 False
51 Adults and Communities 52.0 18.00 False
52 Adults and Communities 53.0 0.00 False
53 Assurance 1.0 0.00 False
54 Assurance 2.0 0.00 False
55 Assurance 3.0 0.00 False
56 Assurance 4.0 13.85 False
57 Assurance 5.0 39.85 True
58 Assurance 6.0 0.00 False
59 Assurance 7.0 0.00 False
60 Assurance 8.0 0.00 False
61 Assurance 9.0 0.00 False
62 Assurance 10.0 0.00 False
63 Assurance 11.0 0.00 False
64 Assurance 12.0 0.00 False
65 Assurance 13.0 0.00 False
66 Assurance 14.0 0.00 False
67 Assurance 15.0 0.00 False
68 Assurance 16.0 0.00 False
69 Assurance 17.0 0.00 False
70 Assurance 18.0 0.00 False
71 Assurance 19.0 0.00 False
72 Assurance 20.0 0.00 False
73 Assurance 21.0 0.00 False
74 Assurance 22.0 0.00 False
75 Assurance 23.0 0.00 False
76 Assurance 24.0 7.30 False
77 Assurance 25.0 0.00 False
78 Assurance 26.0 0.00 False
79 Assurance 27.0 0.00 False
80 Assurance 28.0 0.00 False
81 Assurance 29.0 0.00 False
82 Assurance 30.0 0.00 False
83 Assurance 31.0 0.00 False
84 Assurance 32.0 0.00 False
85 Assurance 33.0 0.00 False
86 Assurance 34.0 0.00 False
87 Assurance 35.0 0.00 False
88 Assurance 36.0 0.00 False
89 Assurance 37.0 0.00 False
90 Assurance 38.0 0.00 False
91 Assurance 39.0 0.00 False
92 Assurance 40.0 0.00 False
93 Assurance 41.0 0.00 False
94 Assurance 42.0 0.00 False
95 Assurance 43.0 0.00 False
96 Assurance 44.0 0.00 False
97 Assurance 45.0 0.00 False
98 Assurance 46.0 83.25 True
99 Assurance 47.0 -1.45 False
100 Assurance 48.0 0.00 False
101 Assurance 49.0 0.00 False
102 Assurance 50.0 0.00 False
103 Assurance 51.0 0.00 False
104 Assurance 52.0 0.00 False
105 Assurance 53.0 0.00 False
106 CSG Managed Budget 2.0 182.08 False
107 CSG Managed Budget 6.0 418.46 True
108 CSG Managed Budget 19.0 30.50 False
109 CSG Managed Budget 20.0 13.40 False
110 CSG Managed Budget 21.0 147.18 False
111 CSG Managed Budget 22.0 84.08 False
112 CSG Managed Budget 25.0 3.47 False
113 CSG Managed Budget 28.0 106.87 False
114 CSG Managed Budget 30.0 150.54 False
115 CSG Managed Budget 31.0 96.20 False
116 CSG Managed Budget 34.0 56.05 False
117 CSG Managed Budget 36.0 12.52 False
118 CSG Managed Budget 39.0 20.00 False
119 CSG Managed Budget 42.0 50.00 False
120 CSG Managed Budget 43.0 78.90 False
121 CSG Managed Budget 49.0 157.06 False
122 Children's Education & Skills 2.0 0.00 False
123 Children's Education & Skills 3.0 981.49 True
124 Children's Education & Skills 4.0 1238.16 True
125 Children's Education & Skills 5.0 549.83 False
126 Children's Education & Skills 6.0 0.00 False
127 Children's Education & Skills 7.0 237.74 False
128 Children's Education & Skills 8.0 4.50 False
129 Children's Education & Skills 9.0 0.00 False
130 Children's Education & Skills 10.0 0.00 False
131 Children's Education & Skills 11.0 33.00 False
132 Children's Education & Skills 14.0 0.00 False
133 Children's Education & Skills 15.0 852.28 True
134 Children's Education & Skills 16.0 0.00 False
135 Children's Education & Skills 18.0 227.50 False
136 Children's Education & Skills 19.0 5.00 False
137 Children's Education & Skills 20.0 160.71 False
138 Children's Education & Skills 21.0 0.00 False
139 Children's Education & Skills 22.0 0.00 False
140 Children's Education & Skills 23.0 299.64 False
141 Children's Education & Skills 24.0 77.47 False
142 Children's Education & Skills 25.0 0.00 False
143 Children's Education & Skills 26.0 0.00 False
144 Children's Education & Skills 27.0 558.32 False
145 Children's Education & Skills 28.0 69.50 False
146 Children's Education & Skills 29.0 0.00 False
147 Children's Education & Skills 30.0 0.00 False
148 Children's Education & Skills 31.0 85.98 False
149 Children's Education & Skills 32.0 0.00 False
150 Children's Education & Skills 33.0 100.50 False
151 Children's Education & Skills 34.0 0.00 False
152 Children's Education & Skills 35.0 0.00 False
153 Children's Education & Skills 36.0 173.94 False
154 Children's Education & Skills 37.0 74.13 False
155 Children's Education & Skills 38.0 0.00 False
156 Children's Education & Skills 39.0 0.00 False
157 Children's Education & Skills 40.0 0.00 False
158 Children's Education & Skills 41.0 503.48 False
159 Children's Education & Skills 42.0 0.00 False
160 Children's Education & Skills 43.0 0.00 False
161 Children's Education & Skills 44.0 0.00 False
162 Children's Education & Skills 45.0 350.00 False
163 Children's Education & Skills 46.0 446.98 False
164 Children's Education & Skills 47.0 485.47 False
165 Children's Education & Skills 48.0 1121.65 True
166 Children's Education & Skills 49.0 629.66 False
167 Children's Education & Skills 50.0 202.41 False
168 Children's Education & Skills 51.0 1441.11 True
169 Children's Education & Skills 52.0 6.98 False
170 Children's Education & Skills 53.0 0.00 False
171 Children's Family Services 1.0 957.04 False
172 Children's Family Services 2.0 1601.09 False
173 Children's Family Services 3.0 9094.78 True
174 Children's Family Services 4.0 6305.44 True
175 Children's Family Services 5.0 10119.26 True
176 Children's Family Services 6.0 3211.83 False
177 Children's Family Services 7.0 1943.75 False
178 Children's Family Services 8.0 2735.92 False
179 Children's Family Services 9.0 3095.46 False
180 Children's Family Services 10.0 320.75 False
181 Children's Family Services 11.0 95.83 False
182 Children's Family Services 12.0 0.00 False
183 Children's Family Services 13.0 0.00 False
184 Children's Family Services 14.0 1261.34 False
185 Children's Family Services 15.0 1944.05 False
186 Children's Family Services 16.0 0.00 False
187 Children's Family Services 17.0 0.00 False
188 Children's Family Services 18.0 2040.11 False
189 Children's Family Services 19.0 50.89 False
190 Children's Family Services 20.0 660.31 False
191 Children's Family Services 21.0 0.00 False
192 Children's Family Services 22.0 0.00 False
193 Children's Family Services 23.0 2530.23 False
194 Children's Family Services 24.0 810.14 False
195 Children's Family Services 25.0 0.00 False
196 Children's Family Services 26.0 0.00 False
197 Children's Family Services 27.0 1077.74 False
198 Children's Family Services 28.0 673.90 False
199 Children's Family Services 29.0 0.00 False
200 Children's Family Services 30.0 0.00 False
201 Children's Family Services 31.0 857.39 False
202 Children's Family Services 32.0 0.00 False
203 Children's Family Services 33.0 986.25 False
204 Children's Family Services 34.0 0.00 False
205 Children's Family Services 35.0 0.00 False
206 Children's Family Services 36.0 2701.74 False
207 Children's Family Services 37.0 619.87 False
208 Children's Family Services 38.0 0.00 False
209 Children's Family Services 39.0 0.00 False
210 Children's Family Services 40.0 314.57 False
211 Children's Family Services 41.0 2248.31 False
212 Children's Family Services 42.0 0.00 False
213 Children's Family Services 43.0 0.00 False
214 Children's Family Services 44.0 0.00 False
215 Children's Family Services 45.0 114.17 False
216 Children's Family Services 46.0 1897.92 False
217 Children's Family Services 47.0 7360.60 True
218 Children's Family Services 48.0 5911.19 True
219 Children's Family Services 49.0 2234.96 False
220 Children's Family Services 50.0 1154.23 False
221 Children's Family Services 51.0 4995.83 True
222 Children's Family Services 52.0 2433.27 False
223 Children's Family Services 53.0 0.00 False
224 Children's Service DSG 2.0 128.90 False
225 Children's Service DSG 3.0 0.00 False
226 Children's Service DSG 4.0 0.00 False
227 Children's Service DSG 5.0 13.85 False
228 Children's Service DSG 6.0 739.75 True
229 Children's Service DSG 7.0 124.71 False
230 Children's Service DSG 8.0 0.00 False
231 Children's Service DSG 9.0 0.00 False
232 Children's Service DSG 10.0 0.00 False
233 Children's Service DSG 11.0 180.45 False
234 Children's Service DSG 12.0 0.00 False
235 Children's Service DSG 13.0 0.00 False
236 Children's Service DSG 14.0 0.00 False
237 Children's Service DSG 15.0 20.00 False
238 Children's Service DSG 16.0 0.00 False
239 Children's Service DSG 17.0 0.00 False
240 Children's Service DSG 18.0 0.00 False
241 Children's Service DSG 19.0 303.77 False
242 Children's Service DSG 20.0 15.77 False
243 Children's Service DSG 21.0 0.00 False
244 Children's Service DSG 22.0 0.00 False
245 Children's Service DSG 23.0 64.48 False
246 Children's Service DSG 24.0 127.30 False
247 Children's Service DSG 25.0 0.00 False
248 Children's Service DSG 26.0 0.00 False
249 Children's Service DSG 27.0 0.00 False
250 Children's Service DSG 28.0 79.98 False
251 Children's Service DSG 29.0 0.00 False
252 Children's Service DSG 30.0 0.00 False
253 Children's Service DSG 31.0 41.98 False
254 Children's Service DSG 32.0 0.00 False
255 Children's Service DSG 33.0 578.61 True
256 Children's Service DSG 34.0 252.05 False
257 Children's Service DSG 35.0 33.00 False
258 Children's Service DSG 36.0 252.96 False
259 Children's Service DSG 37.0 298.16 False
260 Children's Service DSG 38.0 8.15 False
261 Children's Service DSG 39.0 365.93 True
262 Children's Service DSG 40.0 0.00 False
263 Children's Service DSG 42.0 194.32 False
264 Children's Service DSG 43.0 192.20 False
265 Children's Service DSG 44.0 40.00 False
266 Children's Service DSG 45.0 0.00 False
267 Children's Service DSG 46.0 101.28 False
268 Children's Service DSG 47.0 14.37 False
269 Children's Service DSG 48.0 572.87 True
270 Children's Service DSG 49.0 0.00 False
271 Children's Service DSG 50.0 0.00 False
272 Children's Service DSG 51.0 96.39 False
273 Children's Service DSG 53.0 0.00 False
274 Childrens Services 1.0 273.65 False
275 Childrens Services 2.0 2893.47 False
276 Childrens Services 6.0 4036.87 True
277 Childrens Services 7.0 20.00 False
278 Childrens Services 10.0 2199.33 False
279 Childrens Services 11.0 20.00 False
280 Childrens Services 14.0 1229.29 False
281 Childrens Services 15.0 1057.00 False
282 Childrens Services 16.0 1828.23 False
283 Childrens Services 17.0 2919.19 False
284 Childrens Services 18.0 4870.81 True
285 Childrens Services 19.0 351.56 False
286 Childrens Services 20.0 5040.35 True
287 Childrens Services 21.0 3705.73 False
288 Childrens Services 22.0 4909.91 True
289 Childrens Services 23.0 548.94 False
290 Childrens Services 24.0 403.48 False
291 Childrens Services 25.0 3229.64 False
292 Childrens Services 26.0 1890.47 False
293 Childrens Services 27.0 2099.33 False
294 Childrens Services 28.0 2417.57 False
295 Childrens Services 29.0 5472.17 True
296 Childrens Services 30.0 7962.97 True
297 Childrens Services 31.0 3169.25 False
298 Childrens Services 32.0 3104.53 False
299 Childrens Services 36.0 2191.71 False
300 Childrens Services 40.0 938.28 False
301 Childrens Services 41.0 1287.93 False
302 Childrens Services 42.0 289.17 False
303 Childrens Services 43.0 167.60 False
304 Childrens Services 44.0 52.00 False
305 Childrens Services 45.0 1865.51 False
306 Childrens Services 49.0 1233.81 False
307 Commercial 19.0 114.98 False
308 Commercial 32.0 12.00 False
309 Commercial 34.0 217.75 False
310 Commercial 35.0 374.86 True
311 Commercial 39.0 173.60 False
312 Commissioning 1.0 204.56 False
313 Commissioning 2.0 60.60 False
314 Commissioning 3.0 57.19 False
315 Commissioning 4.0 43.95 False
316 Commissioning 5.0 296.29 False
317 Commissioning 6.0 0.00 False
318 Commissioning 7.0 41.67 False
319 Commissioning 8.0 4.50 False
320 Commissioning 9.0 0.00 False
321 Commissioning 10.0 0.00 False
322 Commissioning 11.0 206.60 False
323 Commissioning 12.0 0.00 False
324 Commissioning 14.0 0.00 False
325 Commissioning 15.0 8.20 False
326 Commissioning 16.0 0.00 False
327 Commissioning 17.0 0.00 False
328 Commissioning 18.0 227.50 False
329 Commissioning 19.0 0.00 False
330 Commissioning 20.0 295.00 False
331 Commissioning 21.0 0.00 False
332 Commissioning 22.0 0.00 False
333 Commissioning 23.0 2.50 False
334 Commissioning 24.0 0.00 False
335 Commissioning 25.0 0.00 False
336 Commissioning 26.0 0.00 False
337 Commissioning 27.0 0.00 False
338 Commissioning 28.0 11.50 False
339 Commissioning 29.0 0.00 False
340 Commissioning 30.0 0.00 False
341 Commissioning 31.0 23.64 False
342 Commissioning 32.0 0.00 False
343 Commissioning 33.0 125.04 False
344 Commissioning 34.0 0.00 False
345 Commissioning 35.0 0.00 False
346 Commissioning 36.0 17.04 False
347 Commissioning 37.0 0.00 False
348 Commissioning 38.0 0.00 False
349 Commissioning 39.0 0.00 False
350 Commissioning 40.0 0.00 False
351 Commissioning 41.0 883.22 True
352 Commissioning 42.0 0.00 False
353 Commissioning 43.0 0.00 False
354 Commissioning 44.0 0.00 False
355 Commissioning 45.0 38.79 False
356 Commissioning 46.0 146.60 False
357 Commissioning 47.0 119.62 False
358 Commissioning 48.0 420.00 True
359 Commissioning 49.0 0.00 False
360 Commissioning 50.0 34.86 False
361 Commissioning 51.0 398.20 True
362 Commissioning 52.0 123.42 False
363 Commissioning 53.0 0.00 False
364 Control Accounts 10.0 653.84 False
365 Control Accounts 14.0 909.28 True
366 Control Accounts 24.0 12.50 False
367 Control Accounts 27.0 268.86 False
368 Customer Support Group 2.0 0.00 False
369 Customer Support Group 3.0 17.04 False
370 Customer Support Group 4.0 1159.10 True
371 Customer Support Group 5.0 0.00 False
372 Customer Support Group 7.0 13.39 False
373 Customer Support Group 8.0 0.00 False
374 Customer Support Group 9.0 0.00 False
375 Customer Support Group 10.0 0.00 False
376 Customer Support Group 12.0 0.00 False
377 Customer Support Group 13.0 0.00 False
378 Customer Support Group 15.0 19.25 False
379 Customer Support Group 16.0 0.00 False
380 Customer Support Group 17.0 0.00 False
381 Customer Support Group 20.0 22.92 False
382 Customer Support Group 21.0 0.00 False
383 Customer Support Group 22.0 0.00 False
384 Customer Support Group 23.0 0.00 False
385 Customer Support Group 24.0 23.00 False
386 Customer Support Group 25.0 0.00 False
387 Customer Support Group 26.0 0.00 False
388 Customer Support Group 27.0 0.00 False
389 Customer Support Group 28.0 0.00 False
390 Customer Support Group 29.0 0.00 False
391 Customer Support Group 30.0 0.00 False
392 Customer Support Group 31.0 17.04 False
393 Customer Support Group 32.0 0.00 False
394 Customer Support Group 33.0 0.00 False
395 Customer Support Group 34.0 0.00 False
396 Customer Support Group 35.0 0.00 False
397 Customer Support Group 36.0 96.79 False
398 Customer Support Group 39.0 0.00 False
399 Customer Support Group 41.0 6.58 False
400 Customer Support Group 43.0 0.00 False
401 Customer Support Group 45.0 0.00 False
402 Customer Support Group 46.0 52.51 False
403 Customer Support Group 47.0 187.43 False
404 Customer Support Group 48.0 0.00 False
405 Customer Support Group 49.0 0.00 False
406 Customer Support Group 51.0 165.40 False
407 Customer Support Group 52.0 0.00 False
408 Deputy Chief Operating Officer 2.0 344.94 False
409 Deputy Chief Operating Officer 6.0 19.17 False
410 Deputy Chief Operating Officer 10.0 136.25 False
411 Deputy Chief Operating Officer 11.0 20.00 False
412 Deputy Chief Operating Officer 14.0 135.17 False
413 Deputy Chief Operating Officer 15.0 1279.90 True
414 Deputy Chief Operating Officer 17.0 77.13 False
415 Deputy Chief Operating Officer 18.0 8.32 False
416 Deputy Chief Operating Officer 19.0 149.87 False
417 Deputy Chief Operating Officer 20.0 29.98 False
418 Deputy Chief Operating Officer 21.0 128.21 False
419 Deputy Chief Operating Officer 22.0 957.20 True
420 Deputy Chief Operating Officer 24.0 20.00 False
421 Deputy Chief Operating Officer 25.0 125.06 False
422 Deputy Chief Operating Officer 27.0 61.55 False
423 Deputy Chief Operating Officer 29.0 2240.37 True
424 Deputy Chief Operating Officer 30.0 221.45 False
425 Deputy Chief Operating Officer 32.0 238.69 False
426 Deputy Chief Operating Officer 33.0 -0.26 False
427 Deputy Chief Operating Officer 34.0 123.22 False
428 Deputy Chief Operating Officer 35.0 52.50 False
429 Deputy Chief Operating Officer 36.0 94.68 False
430 Deputy Chief Operating Officer 38.0 1073.91 True
431 Deputy Chief Operating Officer 39.0 534.63 False
432 Deputy Chief Operating Officer 40.0 319.94 False
433 Deputy Chief Operating Officer 41.0 154.60 False
434 Deputy Chief Operating Officer 42.0 354.67 False
435 Deputy Chief Operating Officer 43.0 238.61 False
436 Deputy Chief Operating Officer 44.0 20.00 False
437 Deputy Chief Operating Officer 45.0 114.90 False
438 Deputy Chief Operating Officer 49.0 39.37 False
439 Deputy Chief Operating Officer 50.0 180.00 False
440 Education 2.0 503.52 False
441 Education 6.0 349.76 False
442 Education 7.0 213.12 False
443 Education 10.0 419.78 False
444 Education 11.0 450.24 False
445 Education 24.0 84.00 False
446 Education 28.0 66.91 False
447 Education 32.0 1164.33 True
448 Education 33.0 510.10 False
449 Education 34.0 664.44 False
450 Education 35.0 260.07 False
451 Education 37.0 871.36 True
452 Education 38.0 252.00 False
453 Education 39.0 1531.51 True
454 Education 40.0 158.07 False
455 Education 41.0 50.51 False
456 Education 42.0 1080.05 True
457 Education 43.0 323.89 False
458 Education 44.0 845.36 True
459 Education 45.0 8.41 False
460 Education 50.0 153.00 False
461 Family Services 2.0 3053.45 False
462 Family Services 6.0 621.65 False
463 Family Services 7.0 973.66 False
464 Family Services 10.0 -970.08 False
465 Family Services 11.0 1968.27 False
466 Family Services 15.0 2650.57 False
467 Family Services 19.0 1554.72 False
468 Family Services 23.0 533.99 False
469 Family Services 24.0 214.98 False
470 Family Services 28.0 1991.99 False
471 Family Services 32.0 5492.32 True
472 Family Services 33.0 4443.37 True
473 Family Services 34.0 7192.16 True
474 Family Services 35.0 3670.33 False
475 Family Services 37.0 3039.46 False
476 Family Services 38.0 3918.12 False
477 Family Services 39.0 6926.76 True
478 Family Services 40.0 2165.64 False
479 Family Services 41.0 973.05 False
480 Family Services 42.0 5405.30 True
481 Family Services 43.0 3497.24 False
482 Family Services 44.0 4693.72 True
483 Family Services 45.0 2515.27 False
484 Family Services 50.0 2363.03 False
485 Governance 2.0 -7.50 False
486 Governance 17.0 71.74 True
487 Governance 21.0 88.99 True
488 Governance 26.0 26.98 False
489 Governance 39.0 20.00 False
490 Governance 43.0 20.00 False
491 Governance 50.0 20.00 False
492 HRA 47.0 0.00 False
493 Internal Audit & CAFT 2.0 6.55 False
494 Internal Audit & CAFT 15.0 90.45 False
495 Internal Audit & CAFT 25.0 -0.40 False
496 Internal Audit & CAFT 29.0 109.46 False
497 Internal Audit & CAFT 30.0 45.00 False
498 Internal Audit & CAFT 38.0 40.00 False
499 Internal Audit & CAFT 39.0 294.00 True
500 Internal Audit & CAFT 40.0 82.84 False
501 Internal Audit & CAFT 44.0 3.50 False
502 NSCSO 16.0 0.93 False
503 NSCSO 29.0 130.00 False
504 NSCSO 30.0 4.50 False
505 Parking & Infrastructure 2.0 0.00 False
506 Parking & Infrastructure 7.0 0.00 False
507 Parking & Infrastructure 12.0 0.00 False
508 Parking & Infrastructure 23.0 875.42 True
509 Parking & Infrastructure 24.0 26.66 False
510 Parking & Infrastructure 30.0 0.00 False
511 Parking & Infrastructure 41.0 0.00 False
512 Parking & Infrastructure 48.0 142.84 False
513 Parking & Infrastructure 50.0 0.00 False
514 Public Health 10.0 0.19 False
515 Public Health 17.0 -50.69 False
516 Public Health 25.0 0.00 False
517 Regional Enterprise 3.0 0.00 False
518 Regional Enterprise 4.0 13.85 True
519 Regional Enterprise 11.0 0.00 False
520 Regional Enterprise 45.0 0.00 False
521 Regional Enterprise 48.0 0.00 False
522 Regional Enterprise 51.0 0.00 False
523 Strategic Commissioning Board 26.0 3.60 False
524 Street Scene 1.0 89.00 False
525 Street Scene 2.0 54.00 False
526 Street Scene 11.0 20.00 False
527 Street Scene 16.0 8.48 False
528 Street Scene 17.0 12.68 False
529 Street Scene 23.0 105.43 False
530 Street Scene 24.0 127.84 False
531 Street Scene 25.0 117.35 False
532 Street Scene 27.0 6.45 False
533 Street Scene 28.0 15.00 False
534 Street Scene 29.0 158.74 False
535 Street Scene 31.0 121.69 False
536 Street Scene 33.0 19.54 False
537 Street Scene 34.0 19.17 False
538 Street Scene 35.0 127.76 False
539 Street Scene 36.0 15.00 False
540 Street Scene 38.0 86.92 False
541 Street Scene 39.0 1.30 False
542 Street Scene 42.0 41.00 False
543 Street Scene 43.0 29.00 False
544 Street Scene 44.0 483.96 True
545 Street Scene 45.0 44.35 False
546 Street Scene 50.0 1.30 False
547 Streetscene 1.0 0.00 False
548 Streetscene 2.0 166.86 False
549 Streetscene 3.0 165.99 False
550 Streetscene 4.0 110.76 False
551 Streetscene 5.0 17.04 False
552 Streetscene 6.0 227.50 False
553 Streetscene 7.0 0.00 False
554 Streetscene 8.0 494.11 True
555 Streetscene 9.0 57.87 False
556 Streetscene 10.0 0.00 False
557 Streetscene 11.0 0.00 False
558 Streetscene 12.0 0.00 False
559 Streetscene 13.0 0.00 False
560 Streetscene 14.0 0.00 False
561 Streetscene 15.0 30.00 False
562 Streetscene 16.0 0.00 False
563 Streetscene 18.0 0.00 False
564 Streetscene 20.0 0.00 False
565 Streetscene 21.0 0.00 False
566 Streetscene 22.0 0.00 False
567 Streetscene 23.0 23.96 False
568 Streetscene 24.0 14.50 False
569 Streetscene 25.0 0.00 False
570 Streetscene 26.0 0.00 False
571 Streetscene 27.0 0.00 False
572 Streetscene 28.0 144.23 False
573 Streetscene 29.0 0.00 False
574 Streetscene 30.0 0.00 False
575 Streetscene 32.0 0.00 False
576 Streetscene 33.0 0.00 False
577 Streetscene 34.0 0.00 False
578 Streetscene 35.0 0.00 False
579 Streetscene 36.0 220.70 False
580 Streetscene 37.0 0.00 False
581 Streetscene 38.0 0.00 False
582 Streetscene 39.0 0.00 False
583 Streetscene 40.0 30.79 False
584 Streetscene 41.0 0.00 False
585 Streetscene 42.0 0.00 False
586 Streetscene 43.0 0.00 False
587 Streetscene 44.0 0.00 False
588 Streetscene 45.0 0.00 False
589 Streetscene 46.0 107.25 False
590 Streetscene 47.0 164.80 False
591 Streetscene 48.0 909.47 True
592 Streetscene 49.0 51.72 False
593 Streetscene 50.0 9.05 False
594 Streetscene 51.0 609.08 True
595 Streetscene 52.0 0.00 False

Question 5 part 4¶

anomaly data present in the df¶

In [84]:
only_anomaly
Out[84]:
Service Area Transaction Date JV Value
33 Adults and Communities 34.0 431.05
40 Adults and Communities 41.0 1079.50
57 Assurance 5.0 39.85
98 Assurance 46.0 83.25
107 CSG Managed Budget 6.0 418.46
123 Children's Education & Skills 3.0 981.49
124 Children's Education & Skills 4.0 1238.16
133 Children's Education & Skills 15.0 852.28
165 Children's Education & Skills 48.0 1121.65
168 Children's Education & Skills 51.0 1441.11
173 Children's Family Services 3.0 9094.78
174 Children's Family Services 4.0 6305.44
175 Children's Family Services 5.0 10119.26
217 Children's Family Services 47.0 7360.60
218 Children's Family Services 48.0 5911.19
221 Children's Family Services 51.0 4995.83
228 Children's Service DSG 6.0 739.75
255 Children's Service DSG 33.0 578.61
261 Children's Service DSG 39.0 365.93
269 Children's Service DSG 48.0 572.87
276 Childrens Services 6.0 4036.87
284 Childrens Services 18.0 4870.81
286 Childrens Services 20.0 5040.35
288 Childrens Services 22.0 4909.91
295 Childrens Services 29.0 5472.17
296 Childrens Services 30.0 7962.97
310 Commercial 35.0 374.86
351 Commissioning 41.0 883.22
358 Commissioning 48.0 420.00
361 Commissioning 51.0 398.20
365 Control Accounts 14.0 909.28
370 Customer Support Group 4.0 1159.10
413 Deputy Chief Operating Officer 15.0 1279.90
419 Deputy Chief Operating Officer 22.0 957.20
423 Deputy Chief Operating Officer 29.0 2240.37
430 Deputy Chief Operating Officer 38.0 1073.91
447 Education 32.0 1164.33
451 Education 37.0 871.36
453 Education 39.0 1531.51
456 Education 42.0 1080.05
458 Education 44.0 845.36
471 Family Services 32.0 5492.32
472 Family Services 33.0 4443.37
473 Family Services 34.0 7192.16
477 Family Services 39.0 6926.76
480 Family Services 42.0 5405.30
482 Family Services 44.0 4693.72
486 Governance 17.0 71.74
487 Governance 21.0 88.99
499 Internal Audit & CAFT 39.0 294.00
508 Parking & Infrastructure 23.0 875.42
518 Regional Enterprise 4.0 13.85
544 Street Scene 44.0 483.96
554 Streetscene 8.0 494.11
591 Streetscene 48.0 909.47
594 Streetscene 51.0 609.08
In [85]:
def anomaly_sct(data,date_col,grp_col,val_col,tres):
    """
    arg:same to the old function
    function : scatter plot on whole data anomaly or not
    reutrn: None
    """
    anom_temp=data[val_col]>tres.fillna(0)
#     anom_temp=tres
    plt.figure(figsize=(14,6))
    
    g=sns.scatterplot(data=data,x=date_col,y=val_col,hue=grp_col, legend=False, label='normal',palette='tab20')
    sns.scatterplot(data=data[anom_temp],x=date_col,y=val_col,hue=grp_col,palette='tab20',s=100,marker='x',legend=False,label='doubt')
    g.yaxis.set_major_formatter('${:,.0f}'.format)

    plt.title('anomaly_over_weeks')
    plt.xlabel('week')
    plt.ylabel('Amount')
    plt.legend()
    plt.grid(True)
    plt.tight_layout()
    plt.show()
In [86]:
anomaly_sct(anomaly_encoded_df,'Transaction Date','Service Area','JV Value',treshold)

appendix¶

Question 5 part 5¶

different combinations¶

In [87]:
# Plot anomalies for each service area separately
for service_area, data in anomaly_encoded_df.groupby('Service Area'):
    plt.figure(figsize=(10, 6))
    sns.scatterplot(data=data, x='Transaction Date', y='JV Value', hue='Anomaly', palette='tab10', markers=['o', 'x'], s=100)
    plt.title(service_area)
    plt.xlabel('week')
    plt.ylabel('Amount')
    plt.legend()
    plt.grid(True)
    plt.tight_layout()
    plt.show()

appendix¶

Question 5 part 6¶

top 100 anomaly visual representation¶

In [88]:
#  top 100 anomalies
top_anomalies = anomaly_encoded_df[anomaly_encoded_df['Anomaly']].nlargest(100, 'JV Value')

plt.figure(figsize=(10, 6))
sns.scatterplot(data=top_anomalies, x='Transaction Date', y='JV Value', hue='Service Area', palette='tab20', style='Anomaly', markers=['o', 'x'], s=100)
plt.title('Top 100 Overall Anomalies with Service Area')
plt.xlabel('Transaction Date')
plt.ylabel('Total Transaction Amount')
plt.legend(title='Service Area', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.grid(True)
plt.tight_layout()
plt.show()
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]: